Help creating "between" joins in Power BI / DAX

[Anonymous]

Hi all, I'm struggling to figure out how to solve an issue in Power BI / DAX. The scenario is this - I have survey responses that patients fill out to rate their quality of service. We then run reports by individual survey questions, different departments, etc. that calculate an aggregate score. One example would be:

Question 1, Patient A, 1/1/2017, score = 8 (on scale of 1 - 10)

Question 1, Patient B, 2/1/2017, score = 9 (on scale of 1 - 10)

Based on these two rows of data, the overall score for Question 1 in 2017 would be a 8.5 (simple average).

Now comes the tricky part...I also have a table of benchmark data that contains the question, a date range, and various min and max scores. Based on that, we can figure out the percentile ranking. Sample data for this would look like:

Question 1, 1/1/2017 thru 12/31/2017, min = 8.2, max = 8.3, percentile = 87

Question 1, 1/1/2017 thru 12/31/2017, min = 8.3, max = 8.4, percentile = 88

Question 1, 1/1/2017 thru 12/31/2017, min = 8.4, max = 8.5, percentile = 89

Question 1, 1/1/2017 thru 12/31/2017, min = 8.5, max = 8.6, percentile = 90

Question 1, 1/1/2017 thru 12/31/2017, min = 8.6, max = 8.7, percentile = 91

Based on this, for question 1 we would rate in the 90% percentile (because our 8.5 average is >= the min of 8.5 and < the max of 8.6 for the 90th percentile row)

My issue is - I can't precompute and store these results, as the "average" 8.5 score depends on what is present at the time - which location, deparment, patient demographics are selected. So long and short, after Power BI / DAX computes the average, I then need some way to "look this up" against the benchmark data. Not sure how to do this...I can't build a relationship between the benchmark table and the survey fact table, because relationships can't do "between" type joins...and even if they could, the aggregate average value is only known at report visualization time based on their slicer values.

I see there is a LOOKUPVALUE DAX function, but it seems to require exact matches between the lookup conditions, and doesn't seem able to handle "between" type joins. Any ideas on how this problem might be solved?

Thanks in advance!

Scott

[Anonymous]

So I've played around a bit and I'm able to actually write a FILTER statement that gets me to the exact row I'm looking for:

FILTER(BENCHMARKS,
SCORE >= BENCHMARKS[MIN] && SCORE < BENCHMARKS[MAX]

)

this should return the one row that contains the percentile value I want - and using DAX Studio I can see that it does. The question is - how the heck do I reference a column coming out of that filter?

Thanks,

Scott

[Anonymous]

I think the answer is going to end up being something like:

CALCULATE(MAX(BENCHMARKS[PERCENTILE]),

    FILTER(BENCHMARKS,

             [avg score] >= 'BENCHMARKS'[SCORE]

             && [avg score] < 'BENCHMARKS'[NEXT_HIGH_SCORE]

)

)

Haven't had a chance to test yet...will reply when testing is complete.

Scott