MAX function with multiple conditions for shape map

[madisonb]

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!

[Anonymous · Accepted solution]

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.

[madisonb]

@Anonymous 

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