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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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