Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    See if this is what you had in mind:

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

  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous 

     

    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

     

3 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous 

     

    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's avatar
    Anonymous
    Not applicable
    7 years ago

    See if this is what you had in mind:

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks for the suggestions. 

     

    Regards, Ken