Matrix conditional formating

Matrix conditional formatingFollow
Mouad L
Mouad LAug 29, 2022 10:26 AM
Hello, I have a matrix table with a Year-Month Column and a column “Value”, which I want to apply …
1. Matrix conditional formating

Mouad L
Posted Aug 29, 2022 10:26 AM | view attached
Hello,

I have a matrix table with a Year-Month Column and a column “Value”, which I want to apply a conditional formatting based on the values of 2 slicers : “Sales/Fct” slicer and “VERSION” Slicer.

I have created 2 measures which returns 1 if selected value is Forecast and version is “202201” otherwise 2 for “Sales” and version “202201”.

format_V202201_t =
VAR a =
SELECTEDVALUE ( ‘DATA_Test'[Sales/Fct]) = “forecast”
VAR b =
“202201” IN VALUES ( DATA_Test[Version])
RETURN
IF ( a && b, 1, 2 )

format_V202202_t =
VAR a =
SELECTEDVALUE ( ‘DATA_Test'[Sales/Fct]) = “forecast”
VAR b =
“202201” IN VALUES ( DATA_Test[Version])
RETURN
IF ( a && b, 1, 2 )

The conditionnal formatting works well when I selected only one version. Indeed, the color changes to blue from january 2022, but when I select all the values in my slicers , I don’t have the result I expected. I should have for the first column blue color from January 2022 and for the second column blue color from February 2022.

For the Format_V202202_t : I get what I wanted

I don’t Have the good result that I must get :
For the first column, I must have color Blue for “202201” and not green.

Could someone help me please ?
You will find attached my PBI.

thank you

——————————
Mouad Lda
——————————
Attachment(s)

pbixPB_color.pbix

2. RE: Matrix conditional formating

Kaz Shakir
Posted Aug 29, 2022 07:08 PM
@Mouad L,
The problem you are running into is that in 2022-01 you have both “Sales” and “Forecast” in the Sales/Fct column, so the SELECTEDVALUE function in your formula is not returning what you were expecting. One way that you can solve this is to wrap the SELECTEDVALUE in a CALCULATE, and add a filter argument like so:

​
format_V202201_t =
VAR a =
CALCULATE(SELECTEDVALUE ( ‘DATA_Test'[Sales/Fct]) = “forecast”, DATA_Test[Version]=”202201″)
VAR b =
“202201” IN VALUES ( DATA_Test[Version])
RETURN
IF ( a && b, 1, 2 )

format_V202202_t =
VAR a =
CALCULATE(SELECTEDVALUE ( ‘DATA_Test'[Sales/Fct]) = “forecast”, DATA_Test[Version] = “202202”)
VAR b =
“202202” IN VALUES ( DATA_Test[Version])
RETURN
IF ( a && b, 1, 2 )​

Hope that helps.

Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

3. RE: Matrix conditional formating

Mouad L
Posted Aug 30, 2022 03:55 AM
Hi @Kaz Shakir,
It works !! Thank you so much !

I want to add other “versions” and I want to know if we can create one measure for the values and only one measure for format with all the versions for the matrix conditional formating !

Thank you for your help !

Best regards,

——————————
Mouad L
——————————

4. RE: Matrix conditional formating

Mouad L
Posted Aug 30, 2022 11:08 AM
@Kaz Shakir ​

——————————
Mouad L
——————————

5. RE: Matrix conditional formating

Kaz Shakir
Posted Aug 30, 2022 04:51 PM | view attached
@Mouad L,
There is a way to have just one measure to use for the conditional formatting. I’m not sure if it will work for all of your use cases, but perhaps consider doing the following:

1. Make another calculated column (not a measure), that refers to the [Sales/Fct] column you already created. It looks like this:
Sales/Fct Format =
SWITCH(
DATA_Test[Sales/Fct],
“forecast”, 1,
“Sales”, 2,
0
)​
​
2. Then, use a Matrix visual, rather than a table. Place the Year-Month in the Rows well, Version in the columns well, and Value in the Values well:

3. Then format the “Cell elements”, turn on the “Background color”, and set it like this:

This should result in a similar output to what you currently have:
You could also try placing the Year field and the Month field in the Rows well, instead of the Year-Month field, and you would get this:

and if you click the little “+” in front of one of the Year’s, then it will expand for that year, like this:

You can see this in the attached .pbix file.

Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixPB_color_v2.pbix

6. RE: Matrix conditional formating

Mouad L
Posted Aug 31, 2022 03:19 AM
@Kaz Shakir,

Thank you so much for your help , it works perfectly !
Best regards
​

——————————
Mouad L
——————————