Forum Discussion
Using a measure to get the second highest value - complementary suppression
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:
However when I use this formula in the [Suppression] measure originally posted.. it doesn't work because it tries to break the [NextSmallest] measure down see below table example:
As you can see the Next Smallest measure is accuratley calculating the next smallest value is 3.. However I only see it as a total so the Suppression isn't actually doing anything..
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.Could you post the dax code? I need to do the same. Thank you
