DAX SWITCH with multiple combinations, with overlapping/shared values

[LinLinB]

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

[LinLinB]

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!

[amitchandak]

@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"

)

[LinLinB]

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