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]

This is great Ozeroth! Yeah I was getting stuck on using MIN as filter argument and was also mixing up syntax when I tried to use combinations of TOPN and FILTER with FIRSTNONBLANK. All 3 options you provided increased performance for me by significant amounts. I've been using DAX Studio as well as SQL Server Profiler to analyze query results. Though I'm still a newbie when it comes to reading and optimizing DAX query plans. Here are DAX Studio timings:

Previous Solution:
Total: 296ms , SE CPU: 125ms x0.9 , FE: 156ms 52.7% , SE: 140ms 47.3% , SE Queries: 10 , SE Cache: 2 20.0%

Updated Solutions:
1. Total: 203ms , SE CPU: 110ms x0.7 , FE: 46ms 22.7% , SE: 157ms 77.3% , SE Queries: 10 , SE Cache: 2 20.0%
2. Total: 203ms , SE CPU: 125ms x1.0 , FE: 78ms 38.4% , SE: 125ms 61.6% , SE Queries: 10 , SE Cache: 2 20.0%
3. Total: 218ms , SE CPU: 126ms x0.8 , FE: 61ms 28.0% , SE: 157ms 72.0% , SE Queries: 12 , SE Cache: 3 20.0%

Note my fact table is around 3 million rows, so I'm not sure if that's why storage engine is not running in parallel across cores (not sure if SE CPU only running up to x1.0 because 3 million rows is under 8 million row segment) or if it's because of the settings of the slow server hardware I'm running it on.