DAX - Skip repeating items (duplicates) in a table var

[kcouilla]

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

[Anonymous · Accepted solution]

See if this is what you had in mind:

Measure = 
CALCULATE( 
    MAX( Table1[ Amt ] ), 
    SUMMARIZE( 
        Table1,
        Table1[Cat], 
        "Max", 
        CALCULATE( 
            MAX( Table1[ Amt ] )
        )
    ) 
)

[kcouilla]

Thanks for the suggestions. 

Regards, Ken

[AlB · Accepted solution]

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

[Anonymous · Accepted solution]

See if this is what you had in mind:

Measure = 
CALCULATE( 
    MAX( Table1[ Amt ] ), 
    SUMMARIZE( 
        Table1,
        Table1[Cat], 
        "Max", 
        CALCULATE( 
            MAX( Table1[ Amt ] )
        )
    ) 
)