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.
Hi all
I am still new to Power BI, and one issue is driving me crazy. I hope you can help ...
I am using a Parameter Table to let the user select a "% Warning Level".
When the Warning Level is selected, I want to display a message in every row in a table where a column value is greater than the selected Warning Level. So for example, if the user selects "200%", then every record with a percent greater than 200% will have a text message displayed in the Measure Column saying "Exceeds Level".
From my understanding, I can't use a Calculated Column, as I want the values to recalculate whenever the user selects a different Warning Level, and Calculated Columns are only recalculated when the data is refreshed.
Whenever I use a Measure, I need to use an aggregator, or I get a "single value cannot be determined" error. If I DO use an aggregator, then the value is not calculated on every row, just the subtotals.
Here is something like what I am trying to achieve:
M_Warning =
IF(
HASONEVALUE(WarningLevels[Warning Level 1]),
IF(SignificantDifference[Change] > VALUES(WarningLevels[Warning Level 1]), "Exceeds Level", "")
,"")
Any advice would be greatly appreciated ...
Thanks!
Brad
Solved! Go to Solution.
In this scenario, you can add an Index column for the “SignificantDifference” table and then make a small change to your measure formula. Please refer to following steps.
1. Add an Index column in Query Editor.
2. Modify your measure formula as below.
M_Warning = IF ( HASONEVALUE ( WarningLevels[Warning Level 1] ), IF ( CALCULATE ( SUM ( SignificantDifference[Change] ) ) > VALUES ( WarningLevels[Warning Level 1] ), "Exceeds Level", "" ), "" )
3. Drag a Slicer and Table chart into your canvas.
Best Regards,
Herbert
Thanks Herbert, that was a great, thorough answer. Just what I needed .... 🙂
In this scenario, you can add an Index column for the “SignificantDifference” table and then make a small change to your measure formula. Please refer to following steps.
1. Add an Index column in Query Editor.
2. Modify your measure formula as below.
M_Warning = IF ( HASONEVALUE ( WarningLevels[Warning Level 1] ), IF ( CALCULATE ( SUM ( SignificantDifference[Change] ) ) > VALUES ( WarningLevels[Warning Level 1] ), "Exceeds Level", "" ), "" )
3. Drag a Slicer and Table chart into your canvas.
Best Regards,
Herbert
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 |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |