Matrix Row Conditional Formatting

[Anonymous]

Hi Team

How do i apply conditional formatting on rows in my matrix visual I am using these fields in my Rows : Prod ID, Prod Name, Date and No of Days (Calculated Column) and in Columns using Location names and in Values using status color column (Red, Green, Yellow and Grey)

Prod Id	Prod Name	Date	No of Days	CHA	DEN	MCB	HOL
107	Crash Preventon	03/15/21	38	Red	Yellow	Red	Red
109	Workshop Safety	12/21/21	121	Yellow	Red	Green	Yellow
110	Matrix	12/19/21	119	Green	Yellow	Red	Green

Now Client is askng No of Days column should be highlighted in red if value is greater than 90 days.  I am not sure how to apply conditional formatting on rows. Can anyone suggest how to proceed to solve this.

Regards

[Anonymous · Accepted solution]

Hi @MFelix 

Thanks for your efforts but seems like this solution is not working for my requirement.

When iam trying to create measure first of all iam not able to get my color indicator column (Whole number Data Type) and switched to calculated column with your code and no errors but solution not gettning as expected.

I think if you can check which you provided pbix file you will come to know the issue. or else let me know how can i share my pbix file (if you can share personal main id thru personal message then i wll share my pbix file. if you are ok)😋

[v-kkf-msft]

Hi @Anonymous ,

Try to enable the "Background color" option for the No of Days field, and then create a rule in Advanced controls.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Hi @v-kkf-msft 

My requirement in Matrix. if its table i could have done this anway thanks for your response.

I have given full information in below chain mails if you want to see further information

As of now i have received solution from @MFelix and working on that.

Regards,

Venky

[amitchandak]

@Anonymous , Create a new measure on the top of [No of days] measure and use that in conditional formatting with field value option

Switch( True(),
[No of Days] > 90 , "red",
[No of Days] > 70 , "yellow",
"green"
)

refer my video : https://www.youtube.com/watch?v=RqBb5eBf_I4

or

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

[Anonymous]

@amitchandak Thanks amit for your quick response. I tried but no luck because I have used "No of Days" column is calculated column and when iam trying to create measure for this i can't see the Date column.

Please let me know how to get No of days column using measures

No of Days = datediff (Today (), Table[Date].Date,Day)

Regards

[Anonymous]

Can anyone help me with your suggestions?

No of Days Column should highlghted in red if value is greater than 90 days.

Regards

[Anonymous]

@amitchandak @jdbuchanan71 @Jihwan_Kim @selimovd @MFelix can you one provide your inputs how to proceed with my issue?

Regards

[Jihwan_Kim]

Hi,

Sorry I failed to understand your question.

If it is OK with you, share your sample pbix file with the expected outcome of the visualization.

[MFelix]

Hi @Anonymous ,

If number of days is a calculated column then you just need to use that column to make the condittional formatting on your matrix. The only question is the row value, that is not applicable in a full click, you need to apply that to each value on your matrix.

For each value you need to have the consdittiona formmating set.

Check PBIX file attach.

[Anonymous]

@Jihwan_Kim @MFelix how do i attach pbix file?

Felix, Thanks for your response and the values (REd, Green and Yellow) are in completely background color not in words. I mean My Values section already filled with background colors instead of words.

If you can see "No of Days" calculated column having values 121 and 119 those should be in Red color. So still possble to achieve this solution? Please let me know if you find result.

Regards

[MFelix]

Hi @Anonymous ,

Is this background made with a condittional formatting using a colour based on the fields you are using (CHA, DEN, MCB, HOL)?

If yes then you need to create a condittional formating that overwrites that level of the condittional formatting. Something similar to:

Condittionla formatting = IF( SUM(Table[NumberDays])>= 90 , "Red", ANOTHER CONDITIONAL FORMATTING OPTIONS)

can you share how you are making that condittional formatting?

[Anonymous]

Hi @MFelix 

I am using in my Column section column is "Location Name" which has values of (CHA, DEN, MCB and HOL) and in Values section using "Status Color" column which has the below calculation

Status_Color = 

IF(ARC[Colour_Indicator]=-1,"RED",IF(ARC[Colour_Indicator]=0,"YELLOW",IF(ARC[Colour_Indicator]=1,"GREEN","GREY")))

Colour Indicator column having the data is -1,0,1,2

and finally applying conditional formattning on Status Color column by enablning "Background Color" and in Advanced Controls using "Format by - Feild Value" so that Yellow name in Yellow Color, Red color name in REd color. and also enabled Font Color "Format by - Field Value" so that values section completely turns different colors background.

hopefully you can understand how iam applying. 😀

[MFelix]

Hi @Anonymous ,

If you are using a custom formula for the condittional formatting you just need to wrap your measure in the Number of days try the following measure:

Status_Color =
IF (
    SUM ( Table[Number[No of Days] ) >= 90,
    "RED",
    IF (
        ARC[Colour_Indicator] = -1,
        "RED",
        IF (
            ARC[Colour_Indicator] = 0,
            "YELLOW",
            IF ( ARC[Colour_Indicator] = 1, "GREEN", "GREY" )
        )
    )
)

Also the use of nested if is difficult to maintain try to change it to the following switch formula:

Status_Color =
SWITCH (
    TRUE (),
    SUM ( Table[Number[No of Days] ) >= 90, "RED",
    ARC[Colour_Indicator] = -1, "RED",
    ARC[Colour_Indicator] = 0, "YELLOW",
    ARC[Colour_Indicator] = 1, "GREEN",
    "GREY"
)

[Anonymous · Accepted solution]

Hi @MFelix 

Thanks for your efforts but seems like this solution is not working for my requirement.

When iam trying to create measure first of all iam not able to get my color indicator column (Whole number Data Type) and switched to calculated column with your code and no errors but solution not gettning as expected.

I think if you can check which you provided pbix file you will come to know the issue. or else let me know how can i share my pbix file (if you can share personal main id thru personal message then i wll share my pbix file. if you are ok)😋

[MFelix · Accepted solution]

Sent you a private message so you can send me the link.

[Anonymous]

Hi @MFelix  sent private message