Forum Discussion
IF or SWITCH
- 6 years ago
Hi memarquez ,
Ran this through the Dax Formatter Daxformatter.com. Try it now, and if does not work, let us know what the output is.
Let me know if you have any questions. Remember if it finds a true as it drops through the tests, it stops and returns that value. You may want to break up the code into separate lines even if they have the same values. e.g (2) for readability and debugging.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
NathanielMeasure = SWITCH ( TRUE (), Sheet1[encoded] = 1, 0, Sheet1[learnersbringdrinkingwater] = 1, 1, Sheet1[withdrinkingwaterbutnotregular] = 1 && ( Sheet1[hassafewaterthatistested] = 1 || Sheet1[schoolrequireswaterqualitycertif] = 1 || Sheet1[waterisboiled] = 1 || Sheet1[waterisfiltered] = 1 ), 2, Sheet1[withdrinkingwaterallthetime] = 1 && ( Sheet1[hassafewaterthatistested] = 1 || Sheet1[schoolrequireswaterqualitycertif] = 1 || Sheet1[waterisboiled] = 1 || Sheet1[waterisfiltered] = 1 ), 2, Sheet1[withfreewater] = 1 && Sheet1[withdrinkingwaterallthetime] = 1 && Sheet1[hassafewaterthatistested] = 1 || Sheet1[schoolrequireswaterqualitycertif] = 1 || Sheet1[waterisboiled] = 1 || Sheet1[waterisfiltered] = 1, 3, BLANK () )
Hi Nathaniel_C
I've tried using your proposed syntax. It works. I found no error using the DAX formatter; however, once I plugged that syntax in the Power BI desktop, the results turn to 0-1 range which that should not be the case. As mentioned, the results must be from 0-3 (including blanks if encoded ==0). Much appreciated if you can still help us on this.
I shared to bpsearle the sample dataset.
Thanks,
Marvin
Hi Marvin
The data provided is missing withdrinkingwaterallthetime so I'll modify the DAX to ignore this condition, if you can provide what the values should be as a single column then I can test it fully
Thanks, Brian
- bpsearle6 years agoResolver II
Hi Marvin
Looking at the result of the expression, if you want it to return values 0 to 3 I would say the expression is incorrect. If you supply a separate column with what the expected values are then we can rework the expression to match.
Thanks, Brian