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 (FIND("@Domain2.com",
IF (FIND("@Domain3.com",
IF (FIND("@Domain4.com", table[Email Address],,0), "Canada", "US"))

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

5 Replies

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