Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to Solution.
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"
)
)))))))))))
@kitgo2 Thank so much for your help. It was a great challenge and you did a great effort!. Again, thanks a lot!
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"
)
)))))))))))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |