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...


[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]Probability[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD].01
[/TD]
[TD]14.58[/TD]
[/TR]
[TR]
[TD].02[/TD]
[TD]12.12[/TD]
[/TR]
[TR]
[TD].03[/TD]
[TD]10.75[/TD]
[/TR]
[TR]
[TD].04[/TD]
[TD]9.83[/TD]
[/TR]
[TR]
[TD].05[/TD]
[TD]9.16[/TD]
[/TR]
[TR]
[TD].06[/TD]
[TD]8.65[/TD]
[/TR]
[TR]
[TD].07[/TD]
[TD]8.23[/TD]
[/TR]
[TR]
[TD].08[/TD]
[TD]7.88[/TD]
[/TR]
[TR]
[TD].09[/TD]
[TD]7.59[/TD]
[/TR]
[TR]
[TD].10[/TD]
[TD]7.34[/TD]
[/TR]
[TR]
[TD].11[/TD]
[TD]7.12[/TD]
[/TR]
[TR]
[TD].10[/TD]
[TD]6.93[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]IdentifierID[/TD]
[TD]Group[/TD]
[TD]Size[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]10000[/TD]
[TD]9.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]8400[/TD]
[TD]7.25[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]6000[/TD]
[TD]8.499[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]3000[/TD]
[TD]9.2124[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]4500[/TD]
[TD]12.43[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[TD]2200[/TD]
[TD]11.689[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]C[/TD]
[TD]4800[/TD]
[TD]7.231[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]C[/TD]
[TD]3300[/TD]
[TD]8.82[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]C[/TD]
[TD]8600[/TD]
[TD]10.3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C[/TD]
[TD]5000[/TD]
[TD]9.9930[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]D[/TD]
[TD]1100[/TD]
[TD]10.6510[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]D[/TD]
[TD]1800[/TD]
[TD]13.4[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.