Forum Discussion
Conditional Formatting of Blank Values in Matrix using DAX
Hi Anonymous ,
I tried to find other possible ways (conditional formatting Format style as Field value), but it failed... At the moment it seems to be possible to find a more feasible way which is the way you mentioned in your reply that the blank value will be treated as 0 when calculating the hours, and then the data with "blank values" will show the color correctly...
Best Regards
Community ChampionHi,
Try adding OR to your condition. So something like this: OR(SUM(HoursTable[hours])<8,ISBLANK(SUM(HoursTable[hours])<8))
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
I tried using following expressions, but all of them yield the same incorrect result.
1)Colouring = SWITCH(True, SUM(HoursTable[Hours]) < 8 || ISBLANK(SUM(HoursTable[Hours])), "#ff0000", "")2)Colouring = SWITCH(True, OR(SUM(HoursTable[Hours]) < 8, ISBLANK(SUM(HoursTable[Hours]))), "#ff0000", "")3)Colouring = SWITCH(True, OR(SUM(HoursTable[Hours]) < 8, ISBLANK(SUM(HoursTable[Hours])<8)), "#ff0000", "")Hi Anonymous ,
I created a sample pbix file(see attachment) base on your provided info and make the following changes in the file:
1. Create a measure as below to get the sum of hour per user per date
Working hours = SUM('HoursTable'[Hours])2. Put the above measure to replace the original Values field on the matrix visual and make conditional formatting for this measure just as below screenshot
Best Regards
Hi Anonymous ,
Thank you for your response. Unfortunately, this solution does not quite meet my requirements. I cannot use the rules in Conditional Formatting tab. The presented example is a simplification of a report I am preparing for my organization. In the report, the threshold value (in this case number 8 ) is not a fixed number but instead a dynamic number that can be set using a slicer. To my knowledge, Conditional Formatting rules only allow definition of fixed threshold values, and therefore it cannot be used in my situation. Using a measure created via DAX is the only way I can think of to achieve the goal.
Naturally, there is the possibility to replace blank values with 0. It makes the matrix harder to read, but its the only workaround I could come up with to make this work. Still, I'd like to come up with a DAX solution that colours the blank fields, or come to a conclusion that it is not possible.