I think I can use Switch....

[josevaras]

Hello all - as always, thank you very much - in advance. 

In a nutshell I would like to know if I there is a function in DAX that works the same as a the below Excel formula. 

=
IF(ISNUMBER(SEARCH("AMASale",[@[Partners]])),"Amazon",
IF(ISNUMBER(SEARCH("CstcSale",[@[Partners]])),"Costco",[@[Non-Partners]]))))))

What the formula is doing is looking at the Partners column and for each cell (row) it is returning "Amazon" if within the string it says AMASale and "Costco" if within the string it says CstcSale. If nothing is found (meaning no AMASale or CstcSale) then it will return what ever is listed in the Non-Partners cell. 

If you are all familiar with Excel, apologies for the explanation.

Is there a way for me to do something similar in PBI Desktop? I am thinking within PQE, add a new column and reference the columns I need but I am not sure on the correct DAX to use. Thanks

[HotChilli · Accepted solution]

PQE?     Is this Power Query?

If so, add a column with this code

if Text.Contains([Column1], "AMASale") then "Amazon" else if Text.Contains([Column1], "CstcSale") then "Costco" else [theothercolumn]

[Anonymous]

You can achieve same in dax using stringcontains function and switch statement.
New column=Switch(true(), stringcontains (table[col],"string1"), "expected output",
stringcontains (table[col],"string2"), "outut",table[expectedColumn])

But my recommendation is create it in power query for the better performance.


Thanks
Pravin