Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

highlight weekends in matrix

Hi Dear Community,  I have a Matrix like below, with Days as column and usernames as rows and count on the value.  Now, my end user asked me to highlight the weekends on this matrix. Because he nee...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Here is one way I found to work.

     

    On your Date table, add a column with this expression

     

    Day Color = IF(WEEKDAY('Date'[Date])=1, "Red")

     

    Then, concatenate the expression in your measure that goes in the values area with & " "

    Measure = SUM(Table[Column]) & " "

     

    Then add conditional format to that measure in the matrix, choose Field Value, and your Date[Day Color] field (it will default to First aggregation).

     

    Here is an example I what I saw when I did the above (note one value is blank but still show red).

     

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak , AllisonKennedy  thank you for your replies. but I still have the issue. So let me walk you through what I do: 
I add the MonthDay as my column in the Matrix:

 

Then, I add my measure, which is a simple count : (the measure is

COUNT(ExecutionLogStorage[ReportAction])

Then I created a color column in my Date table, saying if it is Sunday, then Red, otherwise, white. 

Then I set the background color of my measure , with conditioanal formatting and field value of Color column: 

 
 
 
 
 
 
 
 
 
 
 
 
 
 

the month is June, and it is working. 
Now, when I add my Username to my rows, it goes like this :

what I want is for the whole columns which represents Sundays, go red. Like 28 should be red totally. Not just where we have value. 

 

So, I tried to add manually value to blank cells with the measure: 

InteractionCount = if( ISBLANK(COUNT(ExecutionLogStorage[ReportAction]));-99;COUNT(ExecutionLogStorage[ReportAction]))
 
But, no difference, still the same problem: 
 

 

 

What am I doing wrong?

v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

If you have a date column in your table, you can do like this(Please refer to my .pbix file.).

 

If you don't have a date column in your table, please give me a sample data.

 

Best regards,
Lionel Chen

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    v-lionel-msft hello Lionel, 

    Thank you for your reply. But as I described in my post. I need all the cells which are under the 'Sunday's have the red color. something like this :

    so that we can see if we have any value on Sundays. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k , mahoneypat  any ideas in this matter?

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Here is one way I found to work.

         

        On your Date table, add a column with this expression

         

        Day Color = IF(WEEKDAY('Date'[Date])=1, "Red")

         

        Then, concatenate the expression in your measure that goes in the values area with & " "

        Measure = SUM(Table[Column]) & " "

         

        Then add conditional format to that measure in the matrix, choose Field Value, and your Date[Day Color] field (it will default to First aggregation).

         

        Here is an example I what I saw when I did the above (note one value is blank but still show red).

         

         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat