topic Matrix background color by specific values in Rows and Columns in DAX Commands and Tips

Matrix background color by specific values in Rows and Columns

Hernán — Mon, 25 Jul 2022 21:20:10 GMT

Hi team,

I hope you are doing well!

I need to create a matrix with specific background color by rows and columns. For the moment I could specified color only for one condition Rows or Columns. But, I need to specify the color taking in account the values in rows and the values in the columns.

Eg. the below picture represents what I need to :

Image1. My requeriment.

I need to use the specific value in the columns and the specific values in the row for the background-color. Because the background color has a target by "STUDY & Days". The table above.

I create a measure:

Backgroud_color = // I need use rows and column. I tried using AND, && inside switch() but i could it successed.

// By column

SWITCH(

TRUE(),

MAX(Data1[DAYS]) <= 7, "#00FF00",

MAX(Data1[DAYS]) <= 12, "#FFFF00",

MAX(Data1[DAYS]) >12, "#FF0000"

)

Image2. The measure tha I created.

This measure only I can specific the specific values in the column or specific values in the rows. But, my target colors (above image1) request me more accuracy.

Image3. The matrix Image4. Fields selected for the matrix (image3, left)

Did you know how I can create the background color for that matrix?

Perhaps, Are there alternatives for create my requeriment that you want to share with me?

I've tried use:

Switch with AND, Switch with &&

IF with AND, IF with &&

It for use the both conditions, specific values in rows and specific values in columns but I couldn't.

Note: I'm sorry if I have grammatics errors. I'm improving my english.

Thank you so much for your help.

.pbi attached:

(Click here for download Questions.pbi)

Regards,

Hernán

Re: Matrix background color by specific values in Rows and Columns

kitgo2 — Tue, 26 Jul 2022 01:06:36 GMT

Painful but works

in Power Query
Click on Study --> Pivot Columns with %RT as Value

= Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Study]), "Study", "%RT", List.Sum)

Relplace All nulls with 0

click on Days ect.. and Unpivot other columns

= Table.UnpivotOtherColumns(#"Replaced Value2", {"Days"}, "Attribute", "Value")

Rename Attribute --> Study
Rename Value --> %RT

Change text color:
Text Color =
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 7, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 9, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 8, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 12, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 12, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 15, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 13, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 17, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "A" && MAX(Data1[Days]) > 12, "#FF0000",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "B" && MAX(Data1[Days]) > 15, "#FF0000",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "C" && MAX(Data1[Days]) > 13, "#FF0000",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "D" && MAX(Data1[Days]) >17, "#FF0000"
)
)))))))))))

Background Color =
if( Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 7, "#00FF00",
if( Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 9, "#00FF00",
if( Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 8, "#00FF00",
if( Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 12, "#00FF00",
if( Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 12, "#FFFF00",
if( Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 15, "#FFFF00",
if( Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 13, "#FFFF00",
if( Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 17, "#FFFF00",
if( Max(Data1[Study]) = "A" && MAX(Data1[Days]) > 12, "#FF0000",
if( Max(Data1[Study]) = "B" && MAX(Data1[Days]) > 15, "#FF0000",
if( Max(Data1[Study]) = "C" && MAX(Data1[Days]) > 13, "#FF0000",
if( Max(Data1[Study]) = "D" && MAX(Data1[Days]) >17, "#FF0000"
)
)))))))))))

Re: Matrix background color by specific values in Rows and Columns

Hernán — Tue, 26 Jul 2022 14:53:04 GMT

kitgo2 Thank so much for your help. It was a great challenge and you did a great effort!. Again, thanks a lot!