Forum Discussion

Elizz's avatar
Elizz
Frequent Visitor
1 year ago
Solved

Matrix table conditional format based on row and column fields not values

Hi

 

i have this matrix table below (by TCI status and weeks wait), been trying to figure out how to format it so that anything that says TCI after Breach is red, PastTCI is red, TCI before breach is blue- but also if pending anything< than 6 weeks is blue, greater than 5 weeks is red. 

as its not based on the values within the matrix im really struggling to get a measure to work across two tables/fields

 

If there is a way please let me know, really appreciate it.

 

Thanks Liz

 

Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    1 year ago

    Hi Elizz 

    Thanks for reaching out to the Microsoft fabric community forum.


    Create Conditional Formatting DAX Measure -
    MatrixCellColor =
    VAR _Status = SELECTEDVALUE(Appointments[Status])
    VAR _Week = SELECTEDVALUE(Appointments[Week])
    RETURN
    SWITCH(
    TRUE(),
    _Status = "TCI After Breach", "#FF6B6B",
    _Status = "PastTCI", "#FF6B6B",
    _Status = "TCI Before Breach", "#5DADE2",
    _Status = "Pending" && _Week <= 5, "#5DADE2", 
    _Status = "Pending" && _Week > 5, "#FF6B6B", 
    "#FFFFFF" 
    )

    Apply Conditional Formatting
    In Visualizations pane go to Values - dropdown - Conditional formatting - Background color

    Select :
    Format by: Field value
    Based on field: MatrixCellColor


    Please find the below attached .pbix file for your reference.

    Regards,
    Sreeteja

     

12 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    Elizz , Try a measure like below with conditional formatting with the field Value option 

    TCI Color Formatting =
    VAR Status = Max('StatusTable'[TCI Status])
    VAR Weeks = Max('FactTable'[Weeks Wait]) // Assuming the COlumn on Matrix
    RETURN
    SWITCH(TRUE(),
    Status = "TCI after Breach", "#FF0000", // Red
    Status = "PastTCI", "#FF0000", // Red
    Status = "TCI before Breach" && Status = "Pending" && Weeks < 6, "#0070C0", // Blue
    Status = "TCI before Breach" && Status = "Pending" && Weeks >= 6, "#FF0000", // Red
    BLANK()
    )

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    1 year ago

    Hi Elizz 

    Thanks for reaching out to the Microsoft fabric community forum.


    Create Conditional Formatting DAX Measure -
    MatrixCellColor =
    VAR _Status = SELECTEDVALUE(Appointments[Status])
    VAR _Week = SELECTEDVALUE(Appointments[Week])
    RETURN
    SWITCH(
    TRUE(),
    _Status = "TCI After Breach", "#FF6B6B",
    _Status = "PastTCI", "#FF6B6B",
    _Status = "TCI Before Breach", "#5DADE2",
    _Status = "Pending" && _Week <= 5, "#5DADE2", 
    _Status = "Pending" && _Week > 5, "#FF6B6B", 
    "#FFFFFF" 
    )

    Apply Conditional Formatting
    In Visualizations pane go to Values - dropdown - Conditional formatting - Background color

    Select :
    Format by: Field value
    Based on field: MatrixCellColor


    Please find the below attached .pbix file for your reference.

    Regards,
    Sreeteja

     

    • Elizz's avatar
      Elizz
      Frequent Visitor
      1 year ago

      Thank you!  DAX looks to work which is amazing - i was nearly there goggling this its very similar so really good to see it working.. however there is no conditional formatting on values, and it wont let me add it to the conditional format on cell elements?  im on version sept23 (my works fault)  - is that an issue?  

      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        1 year ago

        Hi Elizz ,
        Follow these steps to get it working.

        Click on the Matrix visual. Then, in the Visualizations pane at bottom-right corner, you'll see the 'Values' section where you can drag fields to display data."

         


        Then, click the down arrow next to the field under the 'Values' section. You'll see the 'Conditional formatting' option - select it, and then choose 'Background color' to apply color formatting.

         

        Then, select the field or measure you want to apply the formatting to under 'Based on field'. This lets you control the background color based on a specific value or measure.