Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 , 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]) )
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!
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
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.
That worked like a charm! Thank you so much for your help. Your support is very appreciated!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |