Filtering distinct values in a column w.r.t max value in the next column

[Manohar_Sathya]

Hi All,

I want to filter distinct values in ID Column which has MAX value in Version column. Any Suggestions would help me a lot.

ID     Version
1001    1
1001   2
1001    5
1005   1
1008   2
1005   2
1003   2
1004   1
1003   1
1006   1
1008   1
1001   3
1001   4

Thanks 

MANOHAR

[Icey · Accepted solution]

Hi @Manohar_Sathya ,

Try this:

Max Value Column 1 = 
CALCULATE ( MAX ( 'Table'[Version] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )

Max Value Column 2 = 
CALCULATE (
    MAX ( 'Table'[Version] ),
    FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
)

Max Value Measure 1 = 
CALCULATE ( MAX ( 'Table'[Version] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )

Max Value Measure 2 = 
IF (
    ISFILTERED ( 'Table'[ID] ),
    CALCULATE (
        MAX ( 'Table'[Version] ),
        FILTER ( 'Table', 'Table'[ID] = MAX ( 'Table'[ID] ) )
    ),
    MAX ( 'Table'[Version] )
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Manohar_Sathya]

It is accepted even as a table which looks like the below one;

ID Version
1001 5
1005 2
1008 2
1004 1
1003 2
1006 1

[Manohar_Sathya]

Even an additional Column or a measure would work for me.

[Fowmy]

@Manohar_Sathya 

Can you show me the expected results based on your sample data?

Do you need a table as an output or a measure?

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Manohar_Sathya]

ID Version
1001 5
1001 5
1001 5
1005 2
1008 2
1005 2
1003 2
1004 1
1003 2
1006 1
1008 2
1001 5
1001 5