cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kcouilla Frequent Visitor
Frequent Visitor

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

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:

 

CatEvent Amt 
A123100
B45680
C78970
D1260
C34550
E67825

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Nick_M New Contributor
New Contributor

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

See if this is what you had in mind:

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

Summazirze and Max.png

Super User
Super User

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

Hi @kcouilla 

 

In addition  to @Nick_M'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

 

image.png

3 REPLIES 3
Nick_M New Contributor
New Contributor

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

See if this is what you had in mind:

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

Summazirze and Max.png

Super User
Super User

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

Hi @kcouilla 

 

In addition  to @Nick_M'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

 

image.png

kcouilla Frequent Visitor
Frequent Visitor

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

Thanks for the suggestions. 

 

Regards, Ken