Forum Discussion

PowerBINoob24's avatar
PowerBINoob24
Icon for Resolver I rankResolver I
3 years ago
Solved

Creating a Column using a Nested Find

Hello all: I'm trying to create a column where the value will be based off and email domain. The below is what I have, but it keeps throwing an error.   Country = IF (FIND("@Domain1.com", IF (FIN...
Need Help
  • Barthel's avatar
    Barthel
    3 years ago

    So, if the domain is equal to one of the four then it is Canada otherwise US? Maybe this will work for you: 

    Column =
IF (
    CONTAINSSTRING ( 'Table'[Email Address], "@Domain1.com" )
        || CONTAINSSTRING ( 'Table'[Email Address], "@Domain2.com" )
        || CONTAINSSTRING ( 'Table'[Email Address], "@Domain3.com" )
        || CONTAINSSTRING ( 'Table'[Email Address], "@Domain4.com" ),
    "Cananda",
    "US"
)
Barthel's avatar
Barthel
Icon for Solution Sage rankSolution Sage
3 years ago

Hey PowerBINoob24,
I'm not sure if this is what you're looking for. But you could work with a SWITCH statement. This sets a number of conditions. The first condition that is true is returned. For example:

Column = 
SWITCH ( 
    TRUE,
    CONTAINSSTRING ( 'Table'[Email Address], "@Domain2.com" ), "Cananda",
    CONTAINSSTRING ( 'Table'[Email Address], "@Domain3.com" ), "US"
)

If the mail column contains '@Domain2.com' then 'Canada', if the column contains '@Domain3.com' then 'US' etc.

  • PowerBINoob24's avatar
    PowerBINoob24
    Icon for Resolver I rankResolver I
    3 years ago

    That's not quite what I need.  Need to find the four specific domains, not just the first one it finds.

    • Barthel's avatar
      Barthel
      Icon for Solution Sage rankSolution Sage
      3 years ago

      So, if the domain is equal to one of the four then it is Canada otherwise US? Maybe this will work for you: 

      Column =
IF (
    CONTAINSSTRING ( 'Table'[Email Address], "@Domain1.com" )
        || CONTAINSSTRING ( 'Table'[Email Address], "@Domain2.com" )
        || CONTAINSSTRING ( 'Table'[Email Address], "@Domain3.com" )
        || CONTAINSSTRING ( 'Table'[Email Address], "@Domain4.com" ),
    "Cananda",
    "US"
)
      • PowerBINoob24's avatar
        PowerBINoob24
        Icon for Resolver I rankResolver I
        3 years ago

        Thanks!  That got it.  What is the purpose of the double pipes at the beginning of the lines?