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'm in need of creating a measure that will be able to calculate the second highest value (after 5) in a column and then apply it in another measure..
Some context I need to suppress values less than 5 in the display.. however because there is a total showing if there is only 1 value suppressed per a user selection.. the measure needs to then adjust to suprress up to the next smallest value.. (called complimentary suppression)
I have a measure that can verifies that there is only one value being suppressed in the series [Needs Compliment].. However I'm having trouble capturing the next smallest value beyond 5:
I've been trying to use the rank function however that gives me literally the second largest rather than the next smallest larger than 5..
Because this all needs to be dynamic to user selection I can't use calculated columns unfortunatley..
Any help is hugely appreciated!!
Clarification
Rank actually will work.. because if there is only one value suppressed.. therefore requiring compliment.. the next value would be larger than 5..
Here is the measure I'm using to determine the second value:
Hi @Anonymous ,
Thank your for your clarification firstly.
I still have a little confused about your requirement.
If it is convenient, could you share your data sample which could reproduce your scenario and your desired output so that we could help further on it.
Best Regards,
Cherry
Hi v-piga-msft
I was actually finally able to figure it out.. I appreciate your follow up!
So my goal was to "suppress" the next smallest value in the series when it would otherwise only suppress a single value.. when shown with a total could be easily reverse engineered..
Example series: 10, 4, 7
Suppression Criteria: any value less than 5
So in this case with regular suppression we get: 10, <5, 7.. if shown with the total of 21 it wouldn't take much to figure out what the <5 represents.
Using the Rank function we can apply an asecending order to the series like so:
10 : 3
4 : 1
7 : 2
Next we use an if statement that evaluates when the Rank is "2" checking for if the corresponding value is greater than or less than 5.. If the value is greater than 5 we need complimentary suppression, if it is less than 5 it would be suppressed so we don't need to worry.
Here is the final output:
10,
<5,
Complimentary Suppressed
Now this paired with the total cannot (easily) be reverse engineered for the individual values.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |