Forum Discussion

PeterD20's avatar
PeterD20
New Member
2 years ago
Solved

Create PowerBI Dax Custom Column similar to SQL Case When not in list and wildcards

In PowerBI when creating a custom column how do I reconstruct the SQL case-when statement below similar to a multiple clause if-then-else statement or switch statement. This also contain AND, OR and checking whether strings exist in some list (in this case currency names)

 

Case
    When Account = 2 and Currency in ('EUR','GBP') then 'Europe'
    When Account = 3 and Currency NOT in ('USD','JPY') then 'Emerging Market'
    When Account = 4 then 'Account 4'
    When code like '123%' then 1
    When code like '%222' then 2
    Else Country_Name
    End as my_custom_column

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi PeterD20 ,

     

    My results may not be quite the same as Daniel29195 ; for values that don't satisfy the condition, the value corresponding to the Country_Name field should be returned.


    Refer to the following formula:

    my_custom_column =
SWITCH (
    TRUE (),
    'YourTable'[Account] = 2
        && CONTAINSSTRING ( "EUR,GBP", 'YourTable'[Currency] ), "Europe",
    'YourTable'[Account] = 3
        && NOT CONTAINSSTRING ( "USD,JPY", 'YourTable'[Currency] ), "Emerging Market",
    'YourTable'[Account] = 4, "Account 4",
    CONTAINSSTRING ( 'YourTable'[code], "123%" ), 1,
    CONTAINSSTRING ( 'YourTable'[code], "222%" ), 2,
    TRUE (), 'YourTable'[Country_Name]
) & ""

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    PeterD20 

     

    ouptut

     

     

    Column = 
SWITCH (
    TRUE (),
    AND ( tbl[account] = 2, OR ( tbl[Currency] = "EUR", tbl[Currency] = "GBP" ) ), "Europe",
    AND ( tbl[account] = 3, NOT ( OR ( tbl[Currency] = "USD", tbl[Currency] = "JPY" ) ) ), "Emerging Market",
    tbl[account] = 4, "Account 4",
    LEFT ( tbl[code], 3 ) = "123", "1",
    RIGHT ( tbl[code], 3 ) = "222", "2", 
    tbl[country_name]
)

     

     

    let me know if this helps /

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PeterD20 ,

     

    My results may not be quite the same as Daniel29195 ; for values that don't satisfy the condition, the value corresponding to the Country_Name field should be returned.


    Refer to the following formula:

    my_custom_column =
SWITCH (
    TRUE (),
    'YourTable'[Account] = 2
        && CONTAINSSTRING ( "EUR,GBP", 'YourTable'[Currency] ), "Europe",
    'YourTable'[Account] = 3
        && NOT CONTAINSSTRING ( "USD,JPY", 'YourTable'[Currency] ), "Emerging Market",
    'YourTable'[Account] = 4, "Account 4",
    CONTAINSSTRING ( 'YourTable'[code], "123%" ), 1,
    CONTAINSSTRING ( 'YourTable'[code], "222%" ), 2,
    TRUE (), 'YourTable'[Country_Name]
) & ""

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.