Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi! I am trying to write a DAX measure to use in a matrix to apply conditional formatting.
The row I want to highlight looks like this
I want to highlight the value 4.37 using the following measure
When I apply this measure the value 4.37 does not highlight, but if I flip the sign to (<= 2.90) it highlights the value green. This seems backward to me because it is saying that the value to highlight should be less than or equal to 2.90. I only want to highlight if the value is greater than or equal to 2.90. I could use some help understanding why this is not working.
Also if I wanted to highlight a value less than or equal to 3.50 (<=3.50) or greater than or equal to 4.50 (>=4.50) what would that look like? I tried the below measure and cannot get it to work either.
Hi @Easter-J
Thanks for reaching out on the Microsoft Fabric Community Forum,
As @vicky_ suggested, try using the MAX function instead of SELECTEDVALUE. This approach is more stable, especially when your matrix is aggregating values.
Here’s a revised version of your measure using MAX, with a green highlight as you requested.
Highlight Color =
VAR _id = SELECTEDVALUE('Voluntary 2s Removed'[Id])
VAR _rate = MAX('Voluntary 2s Removed'[Rate]) -- You can also use AVERAGE or MIN depending on your needs
RETURN
SWITCH(TRUE(),
_id = "8.1.12" && _rate >= 2.90, "#A9DFBF", -- Green
_id = "1.1.2" && (_rate <= 3.50 || _rate >= 4.50), "#F9E79F", -- Yellow
"#FFFFFF" -- Default white)
If your matrix is aggregating values, replacing SELECTEDVALUE(Rate) with an aggregation like MAX(Rate) or AVERAGE(Rate) ensures more consistent and reliable conditional formatting.
If this solution works for you, please consider marking it as accepted so others facing a similar issue can benefit too!
Let me know if you need help customizing the colors or logic further
Regards,
Akhil.
I believe the issue might come from the condition:
SELECTEDVALUE('Voluntary 2s Removed'[Rate]) >= 2.90
Double-check your summarisation of the rate column. If there's more than 1 "rate" value, then SELECTEDVALUE would actually be returning blank. The solution is to change "SELECTEDVALUE('Voluntary 2s Removed'[Rate]) >= 2.90" to MAX or SUM (depending on what you use in your table)
If you make the same change to the Highlight Average measure it should work the same.
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |