Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
PrivateAnalytic
Helper IV
Helper IV

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)

PrivateAnalytic_0-1657203356126.png

 

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

TimeDateStudentNameStudentIDCollegeCreditHours15 Staff5 Staff
8:00 AMJanuary 27, 2022Joseph Mann005866Business144951
9:00 AMJanuary 29, 2022John Doe447856Business167779
9:30 AMJanuary 25, 2022Jane Doe555013Law187072
10:00 AMJanuary 25, 2022Ryan Pierce554876Graduate166163
8:00 AMJanuary 26, 2022Jannette Richardson332146Education154951
12:00 PMJanuary 27, 2022Dameon Travis958741Business154547
2:00 PMJanuary 28, 2022Marney Phillips004584Education156365

 

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?

 

PrivateAnalytic_0-1657209788142.png

 

1 ACCEPTED SOLUTION

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"

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

Hi @PrivateAnalytic ,

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

yingyinr_0-1657694385519.png

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.
amitchandak
Super User
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 "
)
)

 

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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"

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.