cancel
Showing results for
Did you mean:
kcouilla 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:

 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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Nick_M 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 ] )
)
)
)```  Super User

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

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 3 REPLIES 3
Nick_M 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 ] )
)
)
)```  Super User

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

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 kcouilla Frequent Visitor

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

Thanks for the suggestions.

Regards, Ken