topic Re: Conditional formatting in Matrix in DAX Commands and Tips

Conditional formatting in Matrix

KHMJ — Fri, 18 Mar 2022 08:29:37 GMT

Hi! I'm trying to do some conditional formatting on my matrix visual. There are a few conditions that I need to have:

1) If value > 100%, colour red

2) Based on the above, all months before the latest month that is red needs to be red too

3) Everything else that is in the first 2 months to be yellow

By creating a measure, I could accomplish 1) and 3), but I need some tips on how to accomplish 2).

Below is a sample data, of which i need the white cells outlined in red to be shaded in red.

Thanks!

Re: Conditional formatting in Matrix

tamerj1 — Fri, 18 Mar 2022 09:47:10 GMT

Hi KHMJ

it depends on the current measure. We meed to modify the existing one not to add a new measure. Can share the code that you are using right now?

Re: Conditional formatting in Matrix

KHMJ — Fri, 18 Mar 2022 15:17:38 GMT

Hi tamerj1 ,

I have a month index that is tied to the months now (i.e. 2022-03 has index 1, 2022-04 has index 2, 2022-05 has index 3 etc).

My current measure for the formatting is:

Conditional Formatting = IF([% Load]>1, "#ff7276", (IF((sum(Table[Month Index])=1 || sum(Table[Month Index])=2), "#ffa500", "#f8f8ff")))

Re: Conditional formatting in Matrix

tamerj1 — Sat, 19 Mar 2022 10:15:09 GMT

Hi KHMJ

You may try the following

Conditional Formatting = VAR CurrentMonth = SELECTEDVALUE ( Table[Month Index] ) VAR LastMonthAbove100 = CALCULATE ( MAX ( Table[Month Index] ), [% Load] > 1, REMOVEFILTERS ( Table[Month Index] ) ) RETURN IF ( CurrentMonth <= LastMonthAbove100, "#ff7276", IF ( CurrentMonth = 1 || CurrentMonth = 2, "#ffa500", "#f8f8ff" ) )

Re: Conditional formatting in Matrix

KHMJ — Sun, 20 Mar 2022 12:36:32 GMT

Thanks tamerj1! I tried and got the following error message though..

"A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Re: Conditional formatting in Matrix

tamerj1 — Sun, 20 Mar 2022 16:04:32 GMT

Ho KHMJ

can you please share some insensitive sample data to work with? What is the code for % Load?

Re: Conditional formatting in Matrix

KHMJ — Mon, 21 Mar 2022 01:19:56 GMT

Hi tamerj1 ,

These are 2 columns that I've created:

Cumulative Load = CALCULATE(SUM(Table[Qty]), FILTER(ALLEXCEPT(Table,Table[Product]),Table[Month Index]<=EARLIER(Table[Month Index])))

Cumulative Capacity = CALCULATE(SUM(Table2[Qty]), FILTER(ALLEXCEPT(Table2,Table2[Product]),Table2[Month Index]<=EARLIER(Table2[Month Index])))

Afterwhich, the measure was created from the 2 columns:

% Load = SUM(Table[Cumulative Load])/SUM(Table2[Cumulative Capacity])

Thanks!

Re: Conditional formatting in Matrix

tamerj1 — Mon, 21 Mar 2022 07:07:15 GMT

Thank you for the reply KHMJ
Here is the correct measure

Conditional Formatting = VAR CurrentMonth = SELECTEDVALUE ( Table[Month Index] ) VAR T1 = CALCULATETABLE ( Table, REMOVEFILTERS ( Table[Year - Month], Table[Year Month Index], Table[Month Index] ) ) VAR T2 = SUMMARIZE ( T1, [Month Index], "%Load", [% Load] ) VAR T3 = FILTER ( T2, [%Load] > 1 ) VAR LastMonthAbove100 = MAXX ( T3, [Month Index] ) VAR Result = IF ( CurrentMonth <= LastMonthAbove100, "#ff7276", IF ( CurrentMonth IN { 2, 3 }, "#ffa500", "#f8f8ff" ) ) RETURN Result

In T1 variable, please make sure to remove the filter from all the columns related to year month.
Have a great day!

Re: Conditional formatting in Matrix

KHMJ — Mon, 21 Mar 2022 08:17:56 GMT

This works, thank you!