Forum Discussion
Rankings Based on Target Values
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!
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" )
3 Replies
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/38490parry2k 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.
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" )