Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Replacing multiple text values

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's avatar
    gingerbread
    3 years ago

    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.

3 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    3 years ago

    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

  • Barthel's avatar
    Barthel
    Solution Sage
    3 years ago

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

     

  • gingerbread's avatar
    gingerbread
    Frequent Visitor
    3 years ago

    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.