Filter visual on the max value of a numerical column

[JemmaD]

Hi,

I have a data table with reference, age (from start date to today) and ID.

Sample data:

ID	Reference	Age (days)
1	A1	9
1	A2	12
2	A1	9
2	A2	12

I want to show this data but with the max of ID only so in this example it would only show 2. But tomorrow, there will be an ID of 3 so I cannot filter on ID = 2. Is there a way to do this either with a measure or some filter logic?

[JemmaD]

Thanks @mdaatifraza5556  and @andrewsommer this solution works.

However I now have something else to add to cause an issue!

There is another column called 'Process_Code' which and each Process Code can have a different Max ID - so with the above logic, if I happent to filter on a Process where the max ID is 1 and not 2 from my example above, it won't show anything because the calculated column is looking across the entire dataset. 

I now need to update the calculated column to find the max ID based on unique Process_Code and show value of 1 for max id per process. Can you help with this?

[mdaatifraza5556]

Hi @JemmaD 

Can you please try the below steps :

1. Instead of calculated column Create a measure
    as you can see the below and rest do the same 

Show Max ID per Process =

     VAR MaxIDForProcess =

         CALCULATE(

             MAX('Table'[ID]),

             ALLEXCEPT('Table', 'Table'[Process_Code] )

         )

RETURN

     IF(MAX('Table'[ID]) = MaxIDForProcess, 1, 0)

If this answers your questions, kindly accept it as a solution and give kudos.

[mdaatifraza5556 · Accepted solution]

Hi @JemmaD 

Please below steps :

1. Create calculated column as below

2. Drag this calculated col to the visual level filter and set 1 to always show the max value, see as below

If this answers your questions, kindly accept it as a solution and give kudos.

[andrewsommer]

You can create a measure that evaluates to 1 only for the rows where the ID is the maximum value, and then use this measure as a visual filter.

IsMaxID :=
VAR MaxID = MAXX(ALL('YourTable'), 'YourTable'[ID])
RETURN IF(MAX('YourTable'[ID]) = MaxID, 1, 0)

Please mark this post as solution if it helps you. Appreciate Kudos.