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 formatti...
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.

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

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!