Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kcouilla
New Member

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
Anonymous
Not applicable

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

View solution in original post

AlB
Super User
Super User

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

 

image.png

View solution in original post

3 REPLIES 3
kcouilla
New Member

Thanks for the suggestions. 

 

Regards, Ken

AlB
Super User
Super User

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

 

image.png

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.