Forum Discussion
Matrix Row Conditional Formatting
Hi ml0911 ,
Apologies for asking again but there must be something in your model that is impacting the relationships or the calculations.
On a previous post you refer:"I just removed a blank row from this table and now it allowing a "one to many" relationship, however this has not fixed the issue with the cells not highlighting."
If now you have a one to many relationship using the values from the one side and making the format has reference you should get the colors on all the rows even for blank values because the context is now the dimension table.
Hi ml0911 ,
The issue occurs because the conditional formatting measure uses SELECTEDVALUE(), which can return BLANK and prevent coloring in some cells. Switching to MAX() in your color measure will ensure consistent formatting across the row, including for cells with 0.
RowColor =
VAR Dept =
MAX('Group Names'[Simple Group Names])
RETURN
SWITCH(
TRUE(),
Dept = "DPD", "#D9E1F2",
Dept = "UPD", "#E2EFDA",
Dept = "TT", "#FFF2CC",
"#FFFFFF"
)Then keep your conditional formatting set to Format by Field value and use this RowColor measure. This approach ensures the color is applied consistently across all cells in the row, including those displaying 0.
Please look at the photos in my post. I am already using a matrix not a table. This changes nothing
Hi ml0911 ,
The issue occurs because the conditional formatting measure uses SELECTEDVALUE(), which can return BLANK and prevent coloring in some cells. Switching to MAX() in your color measure will ensure consistent formatting across the row, including for cells with 0.
RowColor =
VAR Dept =
MAX('Group Names'[Simple Group Names])
RETURN
SWITCH(
TRUE(),
Dept = "DPD", "#D9E1F2",
Dept = "UPD", "#E2EFDA",
Dept = "TT", "#FFF2CC",
"#FFFFFF"
)
Then keep your conditional formatting set to Format by Field value and use this RowColor measure. This approach ensures the color is applied consistently across all cells in the row, including those displaying 0.
I copy and pasted this exact formula and it still is not highlighting 0 values.
- MFelix
Super User
Hi ml0911 ,
I have made a very simple model:
- Dimension table
- Fact table
- Measure with SUM(Table[Value]) + 0
See value below:
Has you can see I did not include any values for some of the sub rows and specifically for a upd.
In the matrix the values are zero.
The values for subrows are coming from the dimension table
Now have added the following measure:
Color = SWITCH(SELECTEDVALUE(sub[sub row]), "dpd", "light Grey", "tt", "Light Green", "upd", "Light Blue")Has you can check the values are all with colors.
Even with more complex models if you get the information from the correct table you should get the correct result.
Can you please let me know how did you setup the matrix.
Hi ml0911 ,
This issue often occurs when some row combinations are missing from your data. Even if your measure returns 0, Power BI may still see those cells as no data, so conditional formatting won’t apply. Make sure you’re using a dimension table with all necessary combinations and valid relationships to the fact table. That should ensure conditional formatting works correctly.