Forum Discussion
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 needs to know which users have the 'Count' value in the weekends.
I have no idea how can I do that. I just create a calculated column with WeekDay(date) function. I don't know what else to do. Help me please
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
12 Replies
- AllisonKennedy
Community Champion
Are you using a DimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
If so, you can use the Day of Week and conditional formatting to highlight the weekends. How do you want the weekends highlighted?AllisonKennedy , I have workDay as a calculated column in my table. I have no Datetable separately?
amitchandak How having a separate data table will help in this case?
- AllisonKennedyYou can use amitchandak 's measure on your existing table, but a separate Date table is highly recommended.
You can also use your WorkDay column as is to do conditional formatting in the matrix, just depends what color you want it and if you also need it to work with the existing conditional formatting you may need to create a custom measure to incorporate both values, so let us know.
- amitchandak
Super User
Anonymous , Hope you have workday in the date table
create a measure like
if(FIRSTNONBLANK(date[Work Day],blank())= 0,"green","red") // change color as per need
if(max(date[Work Day])= 0,"green","red") // change color as per needThen use Conditional formatting with field option.
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
