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

DAX SWITCH with multiple combinations, with overlapping/shared values

Hi - I am trying to get the result in "Case" Column.

 Amount Rating Case 1000 1 1 1000 2 1 1000 3 1 1000 4 2 1000 5 2 1000 3 2

I have tried this:

Switch Column =
SWITCH(
TRUE(),
[Amount] = 1000 && [Rating] in {"1","2","3"}, "Case 1",
[Amount] = 1000 && [Rating] in {"4","5","3"}, "Case 2"
)

But (obviously) Rating with 3 gets swept up in Case 1, and never makes it to Case 2.

I've also tried with || but same result.

Could anyone suggest the right combination of and/or in this statement?

Many thanks

3 REPLIES 3

OK, I think my logic is flawed here. Because logically, there is no way to tell the difference from the rating 3 which belongs to (1,2,3) and (4,5,3).

Thanks for having a look.

Back to the drawing board!

Super User

@LinLinB , In such case it can only be one ,

so change position

SWITCH(
TRUE(),

[Amount] = 1000 && [Rating] in {"4","5","3"}, "Case 2",
[Amount] = 1000 && [Rating] in {"1","2","3"}, "Case 1"

)

Thanks for your response.

Unfortunately, that doesn't work because Rating "3" gets allocated only to Case 1 or Case 2.
Case 1 is on the basis of there being all 3 Ratings true (1-2-3), and Case must have (4-5-3).

At the moment the code just chooses Amount and Rating 1, Amount and Rating 2, Amount and Rating 3 etc.

What would the code for this be?

Thanks

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