Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conditional Formatting of Blank Values in Matrix using DAX

Hello,

 

I have a matrix with working times of users, where rows represent invidividual users and columns are dates. Cells represent users working time in a specific day. I want to highlight all cells with value less than 8 and all blank cells with red colour. I need to do this by creating a measure using DAX and not the rules available in conditional formatting. Example data look like this:

UserTable:

pbrezina_0-1645436466092.png

DateTable:

pbrezina_1-1645436497239.png


HoursTable holding the actual data:

pbrezina_2-1645436529039.png

 

In the model, the UserTable.id is connected to HoursTable.userID and DateTable.Date is connected to HoursTable.Date. It looks like this:

pbrezina_3-1645436587782.png


In the HoursTable, I've created a measure Colouring that determines the colour of each cell in a matrix. If the value in cell is less then 8, assign red colour. If it is black, assign red colour. Otherwise, assign no colour.

pbrezina_4-1645436668932.png

I've created a Matrix visual using my data

pbrezina_5-1645436808218.png

 

And assigned conditional background colour formatting based on the measure value

pbrezina_6-1645436853314.png

 

As a result, I got a Matrix where cells with value less than 8 have red background. However, the blank values have no colour:

pbrezina_7-1645436919754.png


However, if I add the Colouring measure to the matrix as an additional value to be displayed

pbrezina_8-1645436988196.png


Then the background of blank cells is suddenly coloured in red

pbrezina_9-1645437032870.png


Naturally, this is not desirable because the Colouring measure should not be part of the Matrix values

What am I missing here? Why is it behaving like this and the background is coloured properly only when I add the Colouring measure to the Matrix visual? How should I create a DAX measure or modify my data model to achieve what I desire?

1 ACCEPTED SOLUTION

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

yingyinr_0-1646126172444.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
ValtteriN
Super User
Super User

Hi,

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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", "")

 pbrezina_1-1645441176671.png

 

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

yingyinr_0-1645689270399.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yiruan-msft ,

 

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.

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

yingyinr_0-1646126172444.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi. I have the same issue and definetly this must be a bug. When the measure in the matrix visual is empty, the formatting measure is not even evaluated (you can test this by making the formatting measure a constant, and still, the matrix cell will be formatted as if it is empty when the matrix measure is empty, ignoring the formatting measure completely).

 

When you include the formatting measure in the matrix, the formatting measure now is evaluated even then the matrix measure is empty and it works as expected.

 

How can this be fixed?

 

(I can't use the 0 solution because I have a line chart that would display ALL dates because of the 0 even when the dates are filterede out (autoexist wouldn't apply and the line chart would explode ignoring the date filter)).

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors