cancel
Showing results 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

New Member

## IF or SWITCH

Hello,

Can someone help us to correct our DAX provided using SWITCH?

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 ()

)

It is not working. The goal is to come up with different scores of 0-3 using the following logic:

Remains blank if Encoded = 0

1 star if Learners bring water =1

2 stars if with drinking water but not regular =1 or if with drinking water all the time =1 AND (If water has tested=1 OR certified water=1 OR water is filtered =1 OR water is boiled)

3 stars if with free water =1 AND if with drinking water all the time =1 AND (If water has tested=1 OR certified water=1 OR water is filtered =1 OR water is boiled)

0 star if OTHERWISE

1 ACCEPTED SOLUTION
Super User

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.
Nathaniel

``````Measure =
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 ()
)``````

Proud to be a Super User!

6 REPLIES 6
Super User

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.
Nathaniel

``````Measure =
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 ()
)``````

Proud to be a Super User!

New Member

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

Resolver II

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

Resolver 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

Resolver II

Can you provide a sample of your data and I will plug your formula into it and try and see if I can figure this out

Thanks, Brian

New Member

Hello @bpsearle

Here's the sample of our data we are currently working. We'll be glad if you can help us on this.

Thanks,

Marvin

 withdrinkingwaterbutnotregular withnodrinkingwater withfreewater learnersbringdrinkingwater schoolrequireswaterqualitycertif waterisboiled waterisfiltered hassafewaterthatistested 1 0 1 1 0 1 0 1 1 0 1 1 0 1 0 0 0 0 1 1 0 0 1 0 1 0 1 1 0 0 0 0 0 0 1 1 1 1 1 1 1 0 1 1 0 1 0 1 1 0 1 1 1 1 1 1 1 0 1 1 0 0 0 1 0 0 1 1 1 0 1 1 0 0 1 1 1 0 0 1 0 0 0 1 0 0 0 0 0 0 1 1 1 0 0 0 1 0 1 1 0 0 0 0 1 0 1 1 0 0 0 0 1 0 1 1 0 0 0 0

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors