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

[Elizz]

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

[v-sshirivolu · Accepted solution]

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]

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]

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.

[Elizz]

thanks, but when i select the Dax measure it doesnt let me select it.  it wont allow me to select any measures only fields.  i do have a measure in my value as it sums all 3 fact tables (waitlists) together.

[v-sshirivolu]

Hi @Elizz ,
I'm able to select the measure for conditional formatting, as you can see in the screenshot below.

Key Checks Before Applying Conditional Formatting with a Measure

Make sure that your DAX measure is returning a text value (like a hex color code) and was created using "New measure", not a column. Also, ensure you're formatting the correct value field in the matrix.

[Elizz]

Hi, thank you

i can select the measure but it wont apply. i think it could be to do with the version i am on, i can select the measure if i opt for Rules instead if field value but of course that needs to be a number format.  I am also not doing just a sum of the value in the value, i have a measure called WL which i have left in the formula bar as i need to count all 3 different waitlists

[danextian]

Are you perhaps using live connection? Because this setup wont accept a measure that is created locally to be used for conditional formatting. If not, can you show put this measure in a matrix?

[Elizz]

Hi
I am just using desktop, import connection...and will publish on our server, we do not have the cloud. 
not sure what you mean by putting this measure in a matrix, this measure= MatrixCellColor ? i thought matrix needed values/numbers so this did not work.

liz 

[v-sshirivolu]

Hi @Elizz ,

I appreciate your detailed update and the screenshot it really clarifies things. Since you're working with an import connection rather than live, DAX measures and conditional formatting based on field values should function as intended. The MatrixCellColor measure doesn't need to be displayed in the matrix; it's designed to format the WL measure itself.

You're applying the conditional formatting correctly by selecting MatrixCellColor as the base field for WL. If the formatting still isn't showing, it's likely due to the measure returning BLANK() from a context issue, not outputting valid hex codes like "#FF0000", or a possible rendering problem with your Power BI Desktop version. Test MatrixCellColor in a simple matrix to confirm it's returning values everywhere, and consider updating Power BI Desktop to rule out any bugs. You're on the right path ,if you want a review of your measure logic, feel free to share it.

[Elizz]

thank you, i just tried a simple count of a field in the fact table, and it still doesnt apply the conditional format. i even changed the hex codes to a common pallete colour ive used throughout the report.   Im guessing it is down to my version, but getting our IT dept to update it is hard work! haha. i take it there is no other work around on this version?
i will pester my IT dept some more.
i will mark this as helpful as i know it will work with a more uptodate version of PBI.
thank you for all your help

[v-sshirivolu]

Hi @Elizz ,
Thank you ! If the response was helpful and your issue is resolved or awaiting a version update, please consider closing the thread by marking it as solved, as this benefits others in the community. 

[amitchandak]

@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()
)