Forum Discussion

madisonb's avatar
madisonb
Frequent Visitor
2 years ago
Solved

MAX function with multiple conditions for shape map

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's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      madisonb
      Frequent Visitor
      2 years ago

      Anonymous 

       

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