Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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:



DateTable:


HoursTable holding the actual data:

 

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


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.

I've created a Matrix visual using my data

 

And assigned conditional background colour formatting based on the measure value

 

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


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


Then the background of blank cells is suddenly coloured in red


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?

  • Anonymous's avatar
    Anonymous
    4 years ago

    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

6 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion

    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/

    • Anonymous's avatar
      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", "")

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable

        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