Max per category dependent on filter

[Anonymous]

Hope someone can assist.

I want to be able to see the max index per category (in my example, Entry_No) but I want this to dynamically calulcate based on a date slicer.

The measure I am using currently is as below:

MaxIndex = 
    CALCULATE(
        MAX('Table'[Index]),
        ALLEXCEPT('Table', 'Table'[Entry_No])
        )

Here it's working as expected:

But when I change the date slicer:

I want this second example to show MaxIndex as 1 as that's the max index for the date slicer provided. I think it's due to me using ALLEXCEPT in my measure, I've tried playing with different variations of ALL but can't seem to get it working. Anyone able to assist?

[FreemanZ · Accepted solution]

hi @Anonymous 

try like:

try like:
MaxIndex =
VAR _entry = MAX('table'[Entry_No]
RETURN
MAXX(
    FILTER('Table', 'Table'[Entry_No]=_entry),
    'Table'[Index])
)

[Anonymous]

.

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to get the max Index  and it can work on the date slicer.

Here are the steps you can refer to :

(1)This is my test data:

(2)We can create  a  measure like this:

MaxIndex = var _cur_No  =  MAX('Table'[Entry_No])
var _t = FILTER( ALLSELECTED('Table') , 'Table'[Entry_No]=_cur_No)
return
MAXX(_t , [Index])

(3)Then we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thanks Aniya, this worked a treat!

[FreemanZ · Accepted solution]

hi @Anonymous 

try like:

try like:
MaxIndex =
VAR _entry = MAX('table'[Entry_No]
RETURN
MAXX(
    FILTER('Table', 'Table'[Entry_No]=_entry),
    'Table'[Index])
)

[Anonymous]

Thanks @FreemanZ this worked also!

[vicky_]

assuming linkdate is also in the same table, could you try add that to the ALLEXCEPT parameters? 

...  ALLEXCEPT('Table', 'Table'[Entry_No], 'Table'[LinkDate])

[Anonymous]

Thanks Vicky but unfortunately this didn't work for me, it didn't give the max index per my category (Entry_No) - the other solutions provided did work however, I needed to use variables. Thanks for your suggestion anyhow!