Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello. I'm trying to apply conditional formatting to a measure in Power BI desktop. I want the background color of the field for percents 81-100 to be green, 50 - 80 to be yellow, and 0-49 to be red. This is what the rules look like:
This is an screenshot of how the formatting is being applied:
Everything is showing red (but only fields with percents under 50 should show red).
This formatting (above) is being applied to a matrix where there these measures are listed for each state by year and I can open/collapse the years under each state to reveal those state year details.
I used the same rules in another matrix to look at the same measure by year. In this instance, there is more color variation, but it still doesn't follow the rules set in the screen shot above:
Is it possible conditional formatting can't be used in a matrix? I can't see what I am doing wrong.
Solved! Go to Solution.
Hi @TBStacey ,
Please try this:
Measure =
SWITCH(
TRUE(),
[Percent]>=0.81&&[Percent]<=1,"Green",
[Percent]>=0.5&&[Percent]<0.81,"Yellow",
[Percent]>=0&&[Percent]<0.5,"Red"
)
and use field value format style.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thank you for your feedback @Ahmedx
I've corrected the rules as below:
That doesn't seem to have resolved the issue. In the first matrix were there is state date which can be expanded to view individual years below each state, I see this when collapsed (viewing state averages):
The 92 should be green and the 53 should be yellow.
When I expand the matric to view each year below a given state, I see this (no conditional formatting at all):
In the matrix where I only have yearly averages, and the same rules as above, I see this (1 cell formatted, the result are not):
Thank you to anyone who has ideas on what I may be doing wrong!
Hi @TBStacey ,
Please try this:
Measure =
SWITCH(
TRUE(),
[Percent]>=0.81&&[Percent]<=1,"Green",
[Percent]>=0.5&&[Percent]<0.81,"Yellow",
[Percent]>=0&&[Percent]<0.5,"Red"
)
and use field value format style.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hello there and thank you for your idea. I am still learning, so pardon if this is a silly question. When creating the new measure, would the field name for the dataset go between the parantheses after TRUE? I.e., TRUE (dataset field name here),
That is where I put it, but I get this error, "The following syntax error occurred during parsing: Invalid token, Line 3, Offset 10, %."
Hi @TBStacey ,
--When creating the new measure, would the field name for the dataset go between the parantheses after TRUE? I.e., TRUE (dataset field name here)
--The True() function always returns TRUE.
TRUE function (DAX) - DAX | Microsoft Learn
Here, three expressions are matched against TRUE, and if any expression matches first, the value following the expression is output.
SWITCH function (DAX) - DAX | Microsoft Learn
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Thank you! I was able to figure out how to use the new measure based on your additional information and it worked! I appreciate your time and assistance. Have a great day!
you should change the percentage to
100% = 1
81% = 0.81
50% = 0.5
User | Count |
---|---|
99 | |
87 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |