Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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" )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
@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" )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |