topic Re: Return only max value by filter in DAX Commands and Tips

Return only max value by filter

Shawel_pl — Fri, 03 May 2024 08:12:48 GMT

Hello

I'd like to ask you to solve the problem with my dax. I want to return the max value for each filter (product). For example for product one returned value should be 50, for product 2 25, for product 3 100.

Re: Return only max value by filter

DataNinja777 — Fri, 03 May 2024 08:29:42 GMT

Hi Shawel_pl

There are many ways to achieve your required output, and one of them is like below:

Best regards,

Re: Return only max value by filter

AntrikshSharma — Fri, 03 May 2024 08:45:39 GMT

Shawel_pl

Max Length = CALCULATE ( MAX ( 'Table'[Length] ), ALLEXCEPT ( 'Table', 'Table'[Product] ) )

Re: Return only max value by filter

Shawel_pl — Fri, 03 May 2024 10:11:06 GMT

Do you think I could include 2 or more allexcept filters to that formula if I'd like to group it by 2 or more column?

Re: Return only max value by filter

AntrikshSharma — Fri, 03 May 2024 10:53:49 GMT

Yes, for the same table you can include columns from that table in single ALLEXCEPT, when using ALLEXCEPT on Many side you can include even the entire table on the one side: ALLEXCEPT ( Fact, Dimension1, Dimension2, Dimension2[Col1], Dimension3[Col2] )

Re: Return only max value by filter

Shawel_pl — Fri, 03 May 2024 12:39:50 GMT

Thanks

one more question. I have set up filters for example I set up filter to disable product number 2 to be seen in table. Though It's not seen in visualization the value is still being grabbed by dax and if the value from product number 2 is the largest then dax returns it as biggest value among products 1-3 even though number 2 is disabled. How to execute the filter?

I guess I found the way. In my formula I added filter product<>"2"

do you think it's a good way to include the filter within all except function?

Re: Return only max value by filter

Anonymous — Mon, 06 May 2024 02:50:35 GMT

Hi AntrikshSharma , AntrikshSharma ,thanks for the quick replies, I'll add further.

Hi Shawel_pl ,

Regarding your question, I think there is no problem. Dax's calculation environment is divided into external filter conditions and internal filter conditions, the internal filter conditions will override the external filter conditions. 'ALLEXCEPT' function will clear the corresponding external filter conditions, you can only set up the corresponding internal filter conditions in dax.