topic MAX function with multiple conditions for shape map in DAX Commands and Tips

MAX function with multiple conditions for shape map

madisonb — Wed, 29 May 2024 23:22:07 GMT

Hello,

I am creating a shape map with a color saturation of the highest incentives for each state. I am ranking some incentives as more valuable than others (rank column). I want to create a measure that calculates the max of [High] that prioritizes values labeled rank 1 first. If the value in [High] is not a rank 1, then the formula should calculate the max of [High] values with a rank 2 (...and so on up to a rank 5).

I was able to create a calculated column that returns the max [High] values based on the rank. But, I need to be able to do this through a measure to apply to my shape map. I have tested measures with nested IF statements as well as SWITCH TRUE, and cannot get it to calculate properly.

Here is a sample of the data: Link

Example Results: The map should show CO's max incentive as $2,400 with no filters. If I add a filter removing rank 1, the max incentive should update to $2,500.

Hope this is possible!

Re: MAX function with multiple conditions for shape map

Anonymous — Thu, 30 May 2024 02:36:47 GMT

Hi madisonb

Please try this:
Here I create a measure for your reference:

MEASURE = VAR _currentState = SELECTEDVALUE ( 'Table'[State] ) VAR _MinRank = CALCULATE ( MIN ( 'Table'[Rank] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[State] = _currentState ) ) RETURN CALCULATE ( MAX ( 'Table'[High] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[State] = _currentState && 'Table'[Rank] = _MinRank ) )

The result is as follow:

Best Regards

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

Re: MAX function with multiple conditions for shape map

madisonb — Thu, 30 May 2024 04:19:05 GMT

Anonymous

This appears to be working perfectly! Thank you so much for your help!