Conditional Formatting with static and dynamic data

[mundy727]

Hi Power BI Community,

Im currently working with 2 datasets and i have a relationship between the 2, based off of individual name. Ive outlined the tables which make up the columns shown in the table below.

Recruiter name: from dataset #1, display the recruiter name

Recruiter capacity: from dataset #2, displays the recruiter capacity #. This # is static and tied to the recruiter name. For instance, each recruiter has their own capacity #

Total Req Load: from dataset #1, displays the total amount of reqs recruiter is working on at a given time. This value can fluctuate, based on the data being pulled into the data source

What id like to be able to do, is apply conditional formatting to the table visual (example shown below) so that the total req load cell aligned to the recruiter name highlights red, to reflect that if the recruiter shown has a total req load that is >= their recruiter capacity (i.e., Jimmy, Billy, and Jane below total req load exceeds their capacity).

Ive attempted with the rule and value options with conditional formatting that exist, but could use some guidance on how I can achieve this. If there are any additional details i can share that can be helpful, please let me know.

Recruiter Name	Recruiter Capacity	Total Req Load
John Doe	10	15
Jane Doe	11	9
Billy Bob	18	18
Jimmy Smith	15	18

Appreciate any guidance here!

[Houston-ho]

I believe you need another measure, say "overload" boolean to calculate whether load > capcity.

then add conditional formatting when "overload" is true, red, else, green.

[Irwan · Accepted solution]

hello @mundy727 

i dont know how you pulled Load and Capacity, so i am not sure what form Load and Capacity are (column or measure).

but here is one of many ways to do conditional formating. please check if this accomodate your need.

1. If Load and Capacity are column, create new measure with following DAX. you can change the color code based on your requirement.

Color Palette = 
var _Capacity = SELECTEDVALUE('Table'[Recruiter Capacity])
var _Load = SELECTEDVALUE('Table'[Total Req Load])
Return
IF(
    _Load>=_Capacity,
    "#FF0000"
)

2. Pick one or more columns you want to do conditional formating from that down-arrow (you need to do the same step for other column). then go to conditional formating, then choose what kind of formating you want (i used background in this case).

3. choose Field Value, then select the created measure from before.

4. here is the result

if Load and Capacity are measure, then change the DAX into this and the rest are exactly the same.

Color Palette = 
IF(
    [Load]>=[Capacity],
    "#FF0000"
)

Hope this will help.

Thank you.

[mundy727]

Thanks @Irwan , that worked flawlessly! THank you!

[Irwan]

hello @mundy727 

glad to be a help.

Thank you.