DAX Linear Interpolation

[joeshu26]

Hello,

One table is a "lookup" table that contains probabilities and scores. The other is a "fact" table with scores and a column for probabilities. I am looking for a DAX measure that will return the correct probability by linearly interpolating the score in the "fact" table to the "lookup" table...

Probability	Score
.01	14.58
.02	12.12
.03	10.75
.04	9.83
.05	9.16
.06	8.65
.07	8.23
.08	7.88
.09	7.59
.10	7.34
.11	7.12
.10	6.93

<tbody>
</tbody>

IdentifierID	Group	Size	Score
1	A	10000	9.5
2	A	8400	7.25
3	A	6000	8.499
4	A	3000	9.2124
5	B	4500	12.43
6	B	2200	11.689
7	C	4800	7.231
8	C	3300	8.82
9	C	8600	10.3
10	C	5000	9.9930
11	D	1100	10.6510
12	D	1800	13.4

<tbody>
</tbody>

 

[joeshu26]

I currently have this measure to get the weighted average score, so I can see size weighted score by group and would like the same thing with the interpolated probability

Wght Score:=DIVIDE(SUMX(factScore,factScore[Size]*factScore[Score]),SUM(factScore[Size]))

 

[Ozeroth]

I would create a series of intermediate measures to calculate the Scores above/below the Score of interest, the corresponding probabilities, the interpolation % and finally the interpolated probability.

I'm assuming [Wght Score] is the Score we want the interpolated probability for, and the lookup table is called 'Probability' and is hidden so it can't be filtered.

1. Get the max Score that is <= [Wght Score]
   

   [B]Score Lower[/B] :=
   CALCULATE (
       MAX ( 'Probability'[Score] ),
       FILTER (
           'Probability',
           'Probability'[Score] <= [Wght Score]
       )
   )

2. Get the min Score that is >= [Wght Score]
   

   [B]Score Upper[/B] :=
   CALCULATE (
       MIN ( 'Probability'[Score] ),
       FILTER (
           'Probability',
           'Probability'[Score] >= [Wght Score]
       )
   )

3. Get the Probability corresponding to Score Lower
   

   [B]Probability Lower [/B]:=
   CALCULATE (
       MIN ( 'Probability'[Probability] ),
       FILTER (
           'Probability',
           'Probability'[Score] = [Score Lower]
       )
   )

4. Get the Probability corresponding to Score Upper
   

   [B]Probability Upper [/B]:=
   CALCULATE (
       MAX ( 'Probability'[Probability] ),
       FILTER (
           'Probability',
           'Probability'[Score] = [Score Upper]
       )
   )

5. Calculate the interpolation fraction
   

   [B]Interpolation Fraction[/B] :=
   DIVIDE (
       [Wght Score] - [Score Lower],
       [Score Upper] - [Score Lower]
   )

6. Finally calculate the interpolated Probability
   

   [B]Probability Interpolated[/B] :=
   [Probability Lower]
       + [Interpolation Fraction]
       * ( [Probability Upper] - [Probability Lower] )

 

[joeshu26]

Works like a charm, thank you very much

 

[joeshu26]

Hi Guys,

I am looking to optimize the above queries to run quicker and reduce query/cpu duration. Anybody have some optimizations to the above queries to reduce query/dpu duration?
Thanks!

 

[Ozeroth]

Here is a better method than my original version, which should run more quickly (I've tested it myself with DAX Studio)

1. Add a column to your Probability table as shown below. I have called it "Probability Delta per Score Delta". This column is the 'slope' from the current row to the next row, defined as (Change in Probability)/(Change in Score).

For example the value in the first row is (0.02-0.01)/(12.12-14.58) = -0.004065.

I would suggest doing this in Power Query, but can also be done with a calculated column in the PowerPivot table. Having these values pre-computed saves on calculation in the PowerPivot measure.

Probability	Score	Probability Delta per Score Delta
0.01	14.58	-0.004065
0.02	12.12	-0.007299
0.03	10.75	-0.010870
0.04	9.83	-0.014925
0.05	9.16	-0.019608
0.06	8.65	-0.023810
0.07	8.23	-0.028571
0.08	7.88	-0.034483
0.09	7.59	-0.040000
0.1	7.34	-0.045455
0.11	7.12	-0.052632
0.12	6.93

<tbody>
</tbody>

2. Define a new Probability Interpolated measure as follows:

[B]Probability Interpolated :=[/B]
CALCULATE (
    VALUES ( 'Probability'[Probability] ) + VALUES ( 'Probability'[Probability Delta per Score Delta] ) * ( [Wght Score] - VALUES ( 'Probability'[Score] ) ),
    FIRSTNONBLANK( VALUES('Probability'[Score]), IF('Probability'[Score] >= [Wght Score],1) )
)

The 2nd argument of CALCULATE uses FIRSTNONBLANK to find the smallest 'Probability'[Score] that is >= [Wght Score]. This defines the row of the 'Probability' table we want to use.
The 1st argument of CALCULATE evaluates the linear interpolation using this row of 'Probability'.

 

[joeshu26]

Ozeroth, thank you for this DAX wizardry!! I would love to download DAX studio if IT would only let me download it

I decided to create the Calculated Column 'Probability'[Probability Delta per Score Delta] in a SQL query, though Power Query what be an effective alternative.

This took performance of query time from +30 seconds to under 4 seconds!!!

 

[Ozeroth]

You're welcome That's great - wasn't sure how much faster it would be when applied to your real data.

 

[joeshu26]

Would it be possible to move the " FIRSTNONBLANK( VALUES('Probability'[Score]), IF('Probability'[Score] >= [Wght Score],1) ) " filter argument to the storage engine?

For example, replacing the FIRSTNONBLANK with a combination of FILTER with MIN(X) of 'Probability'[Score] >= [Wght Score] ?

 

[Ozeroth]

Hmm interesting - I don't know enough about the ins & outs of storage vs formula engine to tell you for sure, but here are some possibilities. The 3rd one actually uses MIN.
Would be interested in relative performance in your full model - not sure if you have DAX Studio yet?

1. Keep FIRSTNONBLANK but use FILTER

=
CALCULATE (
    VALUES ( 'Probability'[Probability] )
        + VALUES ( 'Probability'[Probability Delta per Score Delta] )
            * ( [Wght Score] - VALUES ( 'Probability'[Score] ) ),
    FIRSTNONBLANK (
        FILTER ( VALUES ( 'Probability'[Score] ), 'Probability'[Score] >= [Wght Score] ),
        1
    )
)

2. Change FIRSTNONBLANK to TOPN and again use FILTER

=
CALCULATE (
    VALUES ( 'Probability'[Probability] )
        + VALUES ( 'Probability'[Probability Delta per Score Delta] )
            * ( [Wght Score] - VALUES ( 'Probability'[Score] ) ),
            TOPN(1, FILTER(VALUES('Probability'[Score]), 'Probability'[Score] >= [Wght Score]),'Probability'[Score],1)


)

3. Use FILTER and MIN function to determine min value
(Note that MIN returns a scalar so can't be directly used as a filter argument for CALCULATE like TOPN/FIRSTNONBLANK can. There could be a better way of doing this with MIN though, not sure.)

=
CALCULATE (
    VALUES ( 'Probability'[Probability] )
        + VALUES ( 'Probability'[Probability Delta per Score Delta] )
            * ( [Wght Score] - VALUES ( 'Probability'[Score] ) ),
    FILTER (
        VALUES ( 'Probability'[Score] ),
        'Probability'[Score]
            = CALCULATE (
                MIN ( 'Probability'[Score] ),
                FILTER ( VALUES ( 'Probability'[Score] ), 'Probability'[Score] >= [Wght Score] )
            )
    )
)