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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

SWITCHT/IF returns the same value when the condition is met and not met

Hello everyone,

 

I'm having some issue with a measure involving a SWITCH statement. I'm not confortable with DAX so maybe you could help me understand what's wrong with my statement.

 

I have two tables. The first one, called 'Global', has a list of individuals and an Attribute Valid that is either "Yes" or "No" for each individual:

IDAttribute Valid
First IDYes
Second IDNo
Third IDNo
Fourth IDYes

 

The second table, called 'AttributeHarmonized' has only two rows:

AttributeValidityHarmonizedAttributeImageURLTypeValue
Non-valid attributeNoImageURL1RedLight
Valid attributeYesImageURL2GreenLight

On that second table, I created the following measure:

 

 

FlagHarmonizedAttribute = SWITCH(True(),
                            CALCULATE(DISTINCTCOUNT(AttributeHarmonized[TypeValue]),
                                      FILTER(AttributeHarmonized,
                                             CONTAINSROW({IF(COUNTROWS(FILTER('Global', 'Global'[Attribute Valid] = "No")) = 1,
                                                             "RedLight",
                                                             "GreenLight")},
                                                         AttributeHarmonized[TypeValue])
                                            )
                                     ) = 1,
                            1,
                            0)

 

 

The point of the above measure is to create a flag that will take the value 1 for the first row when the selected individual (first table, Global) has the value "No" in its Attribute Valid colum, and 0 for the second row. Conversely, I want the flag to take the value 0 for the first row when the selected individual has the value "Yes" in their Attribute Valid colum, and 1 for the second row.

 

This allows me to have visuals that, for example, display "Non-valid attribute" when the selected individual has the value "No" in Attribute Valid and "Valid attribute" when the individual has the value "Yes" in Attribute Valid.

 

Whereas it works perfectly when I select an individual that has the value "Yes", it does not work at all when I select an individual with the value "No". The reason is that the measure FlagHarmonizedAttribute takes the value 1 for both rows in that case.

 

I tried to reproduce the scenario in a .pbix to show it here but everything worked perfectly as intended. I assume the problem comes from my data, then. I can't show my data as they are confidential, but maybe someone could tell me what could possibly make my measure return 1 for both rows?

I also tried with an IF statement instead of a SWITCH one, and the same thing happened.

 

Thank you in advance !

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

Not sure whether I understand correctly,create an index column then try the measure below:

 

_FlagHarmonizedAttribute = 
var _firstrow=CALCULATE(MAX('Global'[Attribute Valid]),FILTER(ALL('Global'),'Global'[Index]=1))
var _flag=
IF(_firstrow="No"&&MAX('Global'[Attribute Valid])="No","GreenLight",
IF(_firstrow="Yes"&&MAX('Global'[Attribute Valid])="Yes","GreenLight","RedLight"))
Return
IF(MAX('AttributeHarmonized'[TypeValue])=_flag,1,0)

 

And you will see:

v-kelly-msft_0-1606980455234.png

 

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

1 REPLY 1
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

Not sure whether I understand correctly,create an index column then try the measure below:

 

_FlagHarmonizedAttribute = 
var _firstrow=CALCULATE(MAX('Global'[Attribute Valid]),FILTER(ALL('Global'),'Global'[Index]=1))
var _flag=
IF(_firstrow="No"&&MAX('Global'[Attribute Valid])="No","GreenLight",
IF(_firstrow="Yes"&&MAX('Global'[Attribute Valid])="Yes","GreenLight","RedLight"))
Return
IF(MAX('AttributeHarmonized'[TypeValue])=_flag,1,0)

 

And you will see:

v-kelly-msft_0-1606980455234.png

 

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.