Forum Discussion

whenriques's avatar
whenriques
Helper I
4 years ago
Solved

Conditional Formatting in a Matrix with Multiple Levels

Do you happen to know how to use conditional formatting to highlight the highest monthly value? See below:

 

 

I tried the DAX measure below:

 

 

Then, going to conditional formatting and using the following:

 

 

I modified the DAX to use just Year and it works, but the challenge is when I put Month and Year on the same matrix.

 

 

Do you have any ideas or thoughts?

 

Thanks in advance for taking a look at it. I appreciate your time and effort.

 

Cheers,

 

Willer

Need Help
  • MarkLaf's avatar
    MarkLaf
    4 years ago

    Does the below work for you? I think the piece you are missing is that you do want to still filter by years, which you can accomplish by adding VALUES( Date[Year] ) as a filter argument of CALCULATE. The ALLSELECTED was making it so all filters from Date were getting removed. E.g.

    MaxInRowContMargin = 
VAR __MaxUnits =
CALCULATE( 
    MAXX( SUMMARIZE( 'Date', 'Date'[Year], 'Date'[Month] ), [ContMargin] ),
    REMOVEFILTERS( 'Date' ), VALUES( 'Date'[Year] ) 
)
RETURN
IF(
    [ContMargin] = __MaxUnits,
    "Light Blue"
)

    Note that I summarized Date by Year and Month columns, but I believe the above should work with your VALUES( [Month Year] ) instead of a summarize, too.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    whenriques , Change _MaxUnits like

     

    var _MaxUnits = calculate (

    maxx(values( Date[Month Year]), [ContMargin]) , allselected() )

     

    or

     

     

    var _MaxUnits = calculate (

    maxx(values( Date[Month Year]), [ContMargin]) , allselected( Date[Year]) )

    • whenriques's avatar
      whenriques
      Helper I
      4 years ago

      Thank you so much for your response amitchandak . By using the recommended DAX, It highlighted the highest value of all years and months.

       

       

      Currently, I have 'Date'[Year] as rows and 'Date'[Month Name] as columns. The goal is to highlight the highest 'Date'[Month Name] of every year. Seems that we are so close... Do you have any thoughts or suggestions?

       

      I appreciate your time and effort!

       

      Regards,

       

      Willer

      • MarkLaf's avatar
        MarkLaf
        Super User
        4 years ago

        Does the below work for you? I think the piece you are missing is that you do want to still filter by years, which you can accomplish by adding VALUES( Date[Year] ) as a filter argument of CALCULATE. The ALLSELECTED was making it so all filters from Date were getting removed. E.g.

        MaxInRowContMargin = 
VAR __MaxUnits =
CALCULATE( 
    MAXX( SUMMARIZE( 'Date', 'Date'[Year], 'Date'[Month] ), [ContMargin] ),
    REMOVEFILTERS( 'Date' ), VALUES( 'Date'[Year] ) 
)
RETURN
IF(
    [ContMargin] = __MaxUnits,
    "Light Blue"
)

        Note that I summarized Date by Year and Month columns, but I believe the above should work with your VALUES( [Month Year] ) instead of a summarize, too.

    • whenriques's avatar
      whenriques
      Helper I
      4 years ago

      Also, I wanted to mention that the matrix has the values switch to rows under Format visual. Is that what might be causing the problem? See below:

       

      Thanks again for your help!