Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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! 🤠:
User | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |