topic Re: Max per category dependent on filter in DAX Commands and Tips

Max per category dependent on filter

Anonymous — Wed, 11 Jan 2023 16:31:42 GMT

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?

Re: Max per category dependent on filter

vicky_ — Wed, 11 Jan 2023 21:25:19 GMT

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

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

Re: Max per category dependent on filter

FreemanZ — Thu, 12 Jan 2023 00:42:22 GMT

hi Anonymous

try like:

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

Re: Max per category dependent on filter

v-yueyunzh-msft — Thu, 12 Jan 2023 02:08:36 GMT

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

Re: Max per category dependent on filter

Anonymous — Thu, 12 Jan 2023 08:25:34 GMT

Re: Max per category dependent on filter

Anonymous — Thu, 12 Jan 2023 09:00:09 GMT

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!

Re: Max per category dependent on filter

Anonymous — Thu, 12 Jan 2023 09:00:50 GMT

Thanks Aniya, this worked a treat!

Re: Max per category dependent on filter

Anonymous — Thu, 12 Jan 2023 09:01:31 GMT

Thanks FreemanZ this worked also!