cancel
Showing results for
Did you mean:
Helper III

## Conditional Formatting/Coloring Values

Hello all!

I have created a matrix and a table on the same tab of a Power BI Report. (See 1st picture below)

The table on the left uses conditional columns which I manually inserted into the table. (For example: If the time is 8:00 AM, then 15 staff is then 49, and so on and so fourth). Both visuals are useing the same times. The values in the matrix are using a column called StudentID, and the values are as Count (Distinct).

I was asked to find a way to color the values on the right as follows:

• If the Value is above the 5 Staff number, then color it Green
• If the Value is below the 15 Staff number, then color it Red
• Ex: On January 25, 2022 the value is 30. This value would be Red
• If Value is in between, then color is Yellow

Here is an example of the data I am using

 Time Date StudentName StudentID College CreditHours 15 Staff 5 Staff 8:00 AM January 27, 2022 Joseph Mann 005866 Business 14 49 51 9:00 AM January 29, 2022 John Doe 447856 Business 16 77 79 9:30 AM January 25, 2022 Jane Doe 555013 Law 18 70 72 10:00 AM January 25, 2022 Ryan Pierce 554876 Graduate 16 61 63 8:00 AM January 26, 2022 Jannette Richardson 332146 Education 15 49 51 12:00 PM January 27, 2022 Dameon Travis 958741 Business 15 45 47 2:00 PM January 28, 2022 Marney Phillips 004584 Education 15 63 65

I am not really 100% sure how this would be able to get formatted the way I am being asked. Would this be using Variables in a calculation? I struggle so much with variables and some DAX Functions. I've tried to do drop down on the values and conditionally format, however that really didn't work for my case.

I did attempt to do conditional formatting. However with the rules, I am not able to do conditional formatting based on both 15 Staff and 5 Staff. So it is being sort of odd with it because I am trying to compare the Value between both 15 and 5 Staff. Is there some sort of DAX Expression that can maybe be written to compare the value between both 15 and 5 Staff?

1 ACCEPTED SOLUTION
Helper III

I'm sort of confused a little on this. So do I create a DAX Expression using both the 15 Staff and 5 Staff?

SWITCH(TRUE()

[15 Staff] < #, "Red",

[5 Staff] > #. "Green",

ELSE "Yellow"

3 REPLIES 3
Community Support

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below:

``````Conditional formatting =
VAR _seltime =
SELECTEDVALUE ( 'Time'[Time] )
VAR _15staff =
CALCULATE (
MAX ( 'Time'[15 Staff] ),
FILTER ( 'Time', 'Time'[Time] = _seltime )
)
VAR _5staff =
CALCULATE (
MAX ( 'Time'[5 Staff] ),
FILTER ( 'Time', 'Time'[Time] = _seltime )
)
RETURN
SWITCH (
TRUE,
[Count of Students] < _15staff, "Red",
[Count of Students] > _5staff, "Green",
[Count of Students] >= [Count of Students]
&& [Count of Students] <= _5staff, "Yellow"
)``````

2. Apply conditional formatting for the Values field

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

@PrivateAnalytic , You can create a color measure based on the condition that should return color and that you can use in conditional formatting using field values

example

Switch(true(),
[SLA] = 1 , "Green" ,
[SLA] >= .5 , "Amber" ,
"Red "
)
)

Helper III

I'm sort of confused a little on this. So do I create a DAX Expression using both the 15 Staff and 5 Staff?

SWITCH(TRUE()

[15 Staff] < #, "Red",

[5 Staff] > #. "Green",

ELSE "Yellow"

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors