Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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:

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.

Show and Tell
Tips and Tricks
Tutorial Requests

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Add +0 to your work hour formula

    Also, move weekday condition as the first condition in Switch

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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. 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

       

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

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

        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.