Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I'm stuck on a DAX issue that seems ridiculously simply but which I can't seem to crack.
I have a set of results in a table var sorted descending by Amt that looks like this:
Cat | Event | Amt |
A | 123 | 100 |
B | 456 | 80 |
C | 789 | 70 |
D | 12 | 60 |
C | 345 | 50 |
E | 678 | 25 |
I need the highest value by Cat without duplicates. To do this I need to remove the second occurrence of Cat C so that the 5th item should be E with the value 25, not C with the value 50.
Note that this is a dynamic calculation so calculated tables will not work.
Any ideas, o wise ones?
Thanks in advance, Ken
Solved! Go to Solution.
See if this is what you had in mind:
Measure = CALCULATE( MAX( Table1[ Amt ] ), SUMMARIZE( Table1, Table1[Cat], "Max", CALCULATE( MAX( Table1[ Amt ] ) ) ) )
Hi @kcouilla
In addition to @Anonymous's suggestion, you could try this:
1. Place all three columns in a table visual and select "Don't summarize" for all of them.
2. Create this measure:
ShowMeasure = IF ( SELECTEDVALUE ( Table1[Amt] ) = CALCULATE ( MAX ( Table1[Amt] ); ALLEXCEPT ( Table1; Table1[Cat] ) ); 1 )
3. Place the measure in the visual level filter of the table visual and select 'Show items when the value:' is --> 1
Thanks for the suggestions.
Regards, Ken
Hi @kcouilla
In addition to @Anonymous's suggestion, you could try this:
1. Place all three columns in a table visual and select "Don't summarize" for all of them.
2. Create this measure:
ShowMeasure = IF ( SELECTEDVALUE ( Table1[Amt] ) = CALCULATE ( MAX ( Table1[Amt] ); ALLEXCEPT ( Table1; Table1[Cat] ) ); 1 )
3. Place the measure in the visual level filter of the table visual and select 'Show items when the value:' is --> 1
See if this is what you had in mind:
Measure = CALCULATE( MAX( Table1[ Amt ] ), SUMMARIZE( Table1, Table1[Cat], "Max", CALCULATE( MAX( Table1[ Amt ] ) ) ) )
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |