Forum Discussion

LinLinB's avatar
LinLinB
Advocate I
3 years ago

DAX SWITCH with multiple combinations, with overlapping/shared values

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

AmountRatingCase
100011
100021
100031
100042
100052
100032

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

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      LinLinB
      Advocate I
      3 years ago

      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
       

       

  • LinLinB's avatar
    LinLinB
    Advocate I
    3 years ago

    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!