Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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 need
Then 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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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?
@Anonymous , If you are using that date in matrix the formula should work
work day = if(WEEKDAY([Date],2)<6,1,0)
@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
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:
What am I doing wrong?
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.
@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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Measures can sit in any table, but it is not in same table. This simple model that I use when working on community solutions is described here.
The shown matrix uses the Date, Product, and Sales table from that model.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
79 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |