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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
LizK75
Frequent Visitor

Switch and comparing values of the same column

Hello Community,

 

My dataset Resume : Player# | Label| Value
Label can be "TOPS", "Competition", "Management", "Training"

Value is the value of the Label

LizK75_0-1711024395824.png

 

I want to show a colour based on the value and the Label

 

ColumnColour = 
VAR ValueCompet = CALCULATE(MAX('Resume'[Value]), 'Resume'[Label] = "Competition")
VAR ValueTrain = CALCULATE(MAX('Resume'[Value]), 'Resume'[Label] = "Training")
VAR RLabel = 'Resume'[Label]
VAR RValue = 'Resume'[Value]
RETURN
    SWITCH(
        TRUE(),
        RLabel = "Management", 
            SWITCH(
                TRUE(),
                RValue >= 150, "Green",
                RValue >= 90, "Yellow",
                TRUE(), "Red"
            ),
        RLabel = "TOPS"
           SWITCH(
                TRUE(),
                ValueTrain < ValueCompet, "Green",
                ValueTrain = ValueCompet, "Yellow",
                TRUE(), "Red"
            )
    )

 

The issue lies with the last label "TOPS", where it consistently displays "Yellow" for all players. When the label is "TOPS", it only considers the value of "TOPS" instead of taking into account the comparison between `ValueEntr` and `ValueCompet`.

 

After several attempts, I focused solely on the condition where Label equals TOP.

 

ColumnColour = 
IF(Resume[Label] = "TOPS", CALCULATE(MAX(Resume[Value]), FILTER(Resume, Resume[Label] = "Training")))

 

and the result is blank.

The idea is how to display the value of "Training" where Label = TOPS. This is the root problem that's why the comparison doesn't work.

How can this be rectified?

 

Thank you

 

 

1 ACCEPTED SOLUTION
JamesFR06
Resolver IV
Resolver IV

HI,

 

Just add allexcept after your max calculations

JamesFR06_0-1711044792656.png

 

View solution in original post

2 REPLIES 2
LizK75
Frequent Visitor

You are my Savior JamesFR06 Thank you so much

Merci beaucoup 😍

JamesFR06
Resolver IV
Resolver IV

HI,

 

Just add allexcept after your max calculations

JamesFR06_0-1711044792656.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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