Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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:
ID | Attribute Valid |
First ID | Yes |
Second ID | No |
Third ID | No |
Fourth ID | Yes |
The second table, called 'AttributeHarmonized' has only two rows:
AttributeValidity | HarmonizedAttribute | ImageURL | TypeValue |
Non-valid attribute | No | ImageURL1 | RedLight |
Valid attribute | Yes | ImageURL2 | GreenLight |
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 !
Solved! Go to Solution.
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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
87 | |
71 | |
63 | |
60 |