topic Re: Replacing multiple text values in DAX Commands and Tips

Replacing multiple text values

Anonymous — Fri, 06 Jan 2023 15:33:58 GMT

Hi,

I'm looking to create a column that will display new text values. Below is the formula I've been using so far, but looking for something that would work using CONTAINS not EQUAL as there are over 2,000 distinct values. So, for example, if the text contains organic then return "Organic" if paid return "Paid".

Column = IF('External Traffic'[Source Medium] = "google / organic", "Organic",

if('External Traffic'[Source Medium] = "Facebook / Paid", "Paid",

"Other"))

I've found other solutions on this forum but it seems to be applying only to numerical values. Any help is much appreciated!

Re: Replacing multiple text values

Nathaniel_C — Fri, 06 Jan 2023 15:53:20 GMT

Hi Anonymous ,

Try using CONTAINS() or CONTAINSSTRING().

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: Replacing multiple text values

Barthel — Fri, 06 Jan 2023 15:53:46 GMT

Hey Anonymous,

You can use the SWITCH function to more easily handle multiple IF conditions at once. For the contains condition, use the CONTAINSSTRING function. For instance:

Column = SWITCH ( TRUE, CONTAINSSTRING ( 'External Traffic'[Source Medium], "google / organic" ), "Organic", CONTAINSSTRING ( 'External Traffic'[Source Medium], "Facebook / Paid" ), "Paid", "Other" )

Re: Replacing multiple text values

gingerbread — Fri, 06 Jan 2023 15:55:22 GMT

You can use the DAX function SWITCH along with the CONTAINS function to check if the text field contains certain values and return different text based on the result.

Here is an example of how you can modify your formula to achieve this:

Column = SWITCH(
TRUE(),
CONTAINS('External Traffic'[Source Medium], "google / organic"), "Organic",
CONTAINS('External Traffic'[Source Medium], "Facebook / Paid"), "Paid",
"Other"
)

This formula will check if the Source Medium field contains the string "google / organic" and return "Organic" if it does, then it will check if it contains the string "Facebook / Paid" and return "Paid" if it does, and finally it will return "Other" if none of the previous conditions are met.