Rankings Based on Target Values

[Anonymous]

I'm currently using a simple measure to calculate a score for a card visual.

CurrentScore = IF([CL % of CQI] < 0.50, "Basic", 
               IF([CL % of CQI] < 0.65, "Emerging", 
               IF([CL % of CQI] < 0.80, "Managed", "Excellent")))

I'm also using the table below as a way to update a custom tachometer visual so it needs to stay in this format.

I cannot figure out the DAX formula to replace the manual entry of 0.50, 0.65, & 0.80 in the measure above. I am wanting to only have to change the values in the table if the target values change. As of today, I have to change the table to fix the tachometer visual and the measure so that the correct score is displayed.

Thanks in advance!

[parry2k]

@Anonymous what is CurrentScore, is it a measure?

What is [CL % of CQI]? Is it a measure? How it is calculated? What you want to replace the fix value with? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@parry2k  Hopefully this helps

Here is the CurrentScore DAX measure I have:

CurrentScore = IF([CL % of CQI] < 0.50, "Basic", 
               IF([CL % of CQI] < 0.65, "Emerging", 
               IF([CL % of CQI] < 0.80, "Managed", "Excellent")))

Here is the CurrentScore DAX measure I want:

CurrentScore = IF([CL % of CQI] < Table1[Target] in row 1, "Basic", 
               IF([CL % of CQI] < Table1[Target] in row 2, "Emerging", 
               IF([CL % of CQI] < Table1[Target] in row 3, "Managed", "Excellent")))

Table1

The only items that will change in Table1 from year to year are the target numbers. When that happens, I will update those numbers in the table accordingly, but as of now, I also have to manually update those numbers in the CurrentScore DAX mesure.

For what it's worth, the [CL % of CQI] measure always comes out to be a decimal number between 0 & 1 (Currently it resides at 0.327 which means CurrentScore returns a score of "Basic" as it should). 

What I would prefer is if I were to lower the target number of 50% to 30% in the first row of Table1, my CurrentScore measure would return "Emerging" without me changing the DAX measure as well.

[parry2k · Accepted solution]

@Anonymous try following measure

CurrentScore =
SWITCH( TRUE(),
[CL % of CQI] < CALCULATE( MAX(Table1[Target] ), Table1[Sort] = 1), "Basic", 
[CL % of CQI] < CALCULATE( MAX(Table1[Target] ), Table1[Sort] = 2), "Emerging", 
[CL % of CQI] < CALCULATE( MAX(Table1[Target] ), Table1[Sort] = 3), "Managed", "Excellent"
)