Replacing multiple text values

[Anonymous]

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!

[gingerbread · Accepted solution]

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.

[Barthel]

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"
)

[Nathaniel_C]

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