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

[PeterD20]

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

[v-kongfanf-msft · Accepted solution]

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.

[Daniel29195]

@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! 🤠: