Solved: Switch, If, Anything it takes, just give me highli...

LYorkToenniges

I am attempting to highlight names in a table as follows:

Missed Day Flag =

CALCULATE(IF([Blue Day]="Not Blue",

IF(AND(SUM('60_DAY_DATA'[Adjusted Commitment]) <> 0, AVERAGE('60_DAY_DATA'[% of Commitment]) < 1),1,0),

IF(AND(SUM('60_DAY_DATA'[Adjusted Commitment]) <> 0, AVERAGE('60_DAY_DATA'[% of Commitment]) < AVERAGE('60_DAY_DATA'[Adjusted Floor Average])), 1, 0)

), KEEPFILTERS('60_DAY_DATA'))

I have also tried :

Missed Day Flag =
VAR NotBlueCase = IF(AND(SUM('60_DAY_DATA'[Adjusted Commitment]) <> 0, SUM('60_DAY_DATA'[% of Commitment]) < 1),1,0)
VAR BlueDayCase = IF(AND(SUM('60_DAY_DATA'[Adjusted Commitment]) <> 0, SUM('60_DAY_DATA'[% of Commitment]) < SUM('60_DAY_DATA'[Adjusted Floor Average])), 1, 0)
RETURN
IF([Blue Day]="Not Blue", NotBlueCase, BlueDayCase)

I know they should be functionally the same but I'm desparate. It seems to be highlighting randomly so I can't even figure out how it is failing.

FURTHER EXPLINATION:

The name should highlight if the following cases are true:

commitment is greater than 0 (either case)

it is a "Blue Day" - result of a measure I have

their % of commitment is less than the adjusted floor average

OR

it is "Not Blue"

their % of commitment is less than 100% (1.0)

other factors include - a date slicer, shares same table as data so no relationship issues there

- a pod slicer - this is a category column in the same table, I don't see this being an issue, it is used to restrict the quanitiy of people in the table. It can be set to ALL.

Apologies if I left out important information, I'm a little burnt out. Please let me know how I can help you help me! I won't be able to respond after 5pm until monday, just wanted to let y'all know I'm not ignoring you, I'm just not at work.

Thank you a thousand % for any help you can provide.

v-tejrama

Hi @LYorkToenniges ,
Thanks for reaching out to the Microsoft fabric community forum.

The original issue was that names in a Power BI table needed to be highlighted only when certain conditions are met:
So instead of that, I used a direct color logic in DAX.

First, I created a new measure to apply color conditionally, not using the flag.

Here's the DAX I used:

NameColor = 
VAR _BlueDay = SELECTEDVALUE('SampleData'[Blue Day])
VAR _AdjCommitment = SELECTEDVALUE('SampleData'[Adjusted Commitment])
VAR _PctCommitment = SELECTEDVALUE('SampleData'[% of Commitment])
VAR _AdjFloorAvg = SELECTEDVALUE('SampleData'[Adjusted Floor Average])
RETURN
IF(
    _AdjCommitment > 0 &&
    (
        (_BlueDay = "Blue" && _PctCommitment < _AdjFloorAvg) ||
        (_BlueDay = "Not Blue" && _PctCommitment < 1)
    ),
    "Red",
    "Black"
)

In Table visual, and applied conditional formatting on the Name column:

Right-click on "Name" in the visual - Conditional formatting - Font color
In the popup:
- Format style: Field value
- Apply to: Values only
- Based on field: Select the NameColor measure

Once applied, this correctly highlighted only the names that matched the logic. In my case, only "Bob" was highlighted in red exactly as expected based on the values.

This approach worked perfectly without needing a separate 1/0 flag, and it respects the row context cleanly without any random highlights. Hope this helps!

Please find the attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support.

View solution in original post

v-tejrama

Hi @LYorkToenniges ,
Thanks for reaching out to the Microsoft fabric community forum.

The original issue was that names in a Power BI table needed to be highlighted only when certain conditions are met:
So instead of that, I used a direct color logic in DAX.

First, I created a new measure to apply color conditionally, not using the flag.

Here's the DAX I used:

NameColor = 
VAR _BlueDay = SELECTEDVALUE('SampleData'[Blue Day])
VAR _AdjCommitment = SELECTEDVALUE('SampleData'[Adjusted Commitment])
VAR _PctCommitment = SELECTEDVALUE('SampleData'[% of Commitment])
VAR _AdjFloorAvg = SELECTEDVALUE('SampleData'[Adjusted Floor Average])
RETURN
IF(
    _AdjCommitment > 0 &&
    (
        (_BlueDay = "Blue" && _PctCommitment < _AdjFloorAvg) ||
        (_BlueDay = "Not Blue" && _PctCommitment < 1)
    ),
    "Red",
    "Black"
)

In Table visual, and applied conditional formatting on the Name column:

Right-click on "Name" in the visual - Conditional formatting - Font color
In the popup:
- Format style: Field value
- Apply to: Values only
- Based on field: Select the NameColor measure

Once applied, this correctly highlighted only the names that matched the logic. In my case, only "Bob" was highlighted in red exactly as expected based on the values.

This approach worked perfectly without needing a separate 1/0 flag, and it respects the row context cleanly without any random highlights. Hope this helps!

Please find the attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support.

LYorkToenniges

Thank you so much. I figured out a work around, but this is more susinct. The issue turned out to be the measure referenced. I needed to use calculate to control the filters. It was being evaluated line by line - which is not what it's designed for - so it was throwing up crazy results. I will incorporate aspects of this in my solution for sure. Thank you!

v-tejrama

Hi @LYorkToenniges ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you,

Tejaswi.