Return only max value by filter

[Shawel_pl]

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. 

 

[DataNinja777 · Accepted solution]

Hi @Shawel_pl 

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

Best regards,

[AntrikshSharma · Accepted solution]

@Shawel_pl 

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

[v-zhouwen-msft · Accepted solution]

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.

[AntrikshSharma · Accepted solution]

@Shawel_pl 

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

[Shawel_pl]

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? 

[AntrikshSharma]

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] )

[Shawel_pl]

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? 

[DataNinja777 · Accepted solution]

Hi @Shawel_pl 

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

Best regards,