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

Matrix-Conditional formating based on another field

@ links to members, content
 
I recently received a solution to color background for a table.  My requirements have changed and I need to format background color for matrix.  I need the background color for work hours to change based on the value in the CategoryDesc field and WorkDate field.  the weekend days(Saturday and Sunday) these columns should be yellow.  If the work hours are blank the color should be pink. Any help would be greatly appreciated.  The below DAX formula works in a table but not in the matrix.
 
EmployeeCategoryDesc WorkDates      
EmployeeCategoryDescColorCode2/2/20192/3/20192/4/20192/5/20192/6/20192/7/20192/8/2019
Jane DowLabor-Daily

Lime

8888  10
Jane DoeLabor-SalaryLime   899 
Jane DowHolidayBlack9 8  8 
Jane DoeVacationOrange88 10   

 

Here is my example:

sample.jpg

I added to this DAX formula, however, I cant get the weekends to turn yellow or where there is no value in work hours to turn pink.

form.jpg

7 REPLIES 7
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

You can refer to this :https://community.powerbi.com/t5/Desktop/Conditional-color-formatting-on-Matrix-based-on-dimension-values/m-p/836471

If the problem persists,could you please share a PBIX file through cloud service like OneDrive.(Please mask any sensitive data before uploading)

 

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

 

Anonymous
Not applicable

@V-lianl-msft  Sorry, this is not working for me, maybe something I am doing wrong .

I need to color code the background color for work hours based on the categorydesc field. 

 

Labor - Daily - green

Labor - Hourly - green

Labor - Salary - green

Vacation - orange

Holiday - black

When the work hours are 0 or no value the color should be pink.

When the date falls on a weekend that entire column should be yellow regardless if hours are reported.

 

Please see my sample.  I do not know how to save this in a clound service.

 

example.jpg

Hi @Anonymous ,

 

Based on the sample data you provided, I get the result you expect.test_0319.PNG

Measure = SWITCH(TRUE(),
                WEEKDAY(MAX(Sheet1[weekdate]),2)in{6,7},"yellow",
                MAX(Sheet1[hours])=0||ISBLANK(MAX(Sheet1[hours])),"pink",
                MAX(Sheet1[categorydesc ])="Holiday","black",
                MAX(Sheet1[categorydesc ])="Vacation","orange",
                MAX(Sheet1[categorydesc ])="Labor - Daily","green",
                MAX(Sheet1[categorydesc ])="Labor - Hourly ","green",
                MAX(Sheet1[categorydesc ])="Labor - Salary","green",
                "white")

Here is the sample pbix

 

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

Anonymous
Not applicable

@V-lianl-msft Here is my pbix file.  All of the colors are not working. 

 

file 

 

when there are no hours the background should be pink.  Also, it is only shading saturdays not sundays.  please let me know if you can access the file, if not please send me an email address.  Thanks a lot for your hel

Hi @Anonymous ,

 

Unfortunately, we are performing conditional formatting on "work hours" and can only judge when "work hours" has value.

 

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

amitchandak
Super User
Super User

Add +0 to your work hour formula

Also, move weekday condition as the first condition in Switch

 

Anonymous
Not applicable

@amitchandak When i move weeknumber condition to first condition in switch, everything changes to yellow.  I dont know how to add the work hours to this switch. 

new sample.pngnew sample2.png

 

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.