Hi Marianne,

The SWITCH function is not case sensitive but it does require an exact match and you can't use wildcards. So, you have to account for all permutations of Region in your formula, otherwise it will return "(not set)".

For example this formula should work:

Segment = SWITCH([NetworkLocation];

     "region nord";"Regioner";

    "vest region";"Regioner";

    "kommune";"Kommuner";

     "";"Andre";

     "(not set)")

If you're still having problems please share a file with a sample of your exact data so we can see if there are any other characters in the fields that might be causing the error.

Kind regards,

Mynda

Hi Mynda,

Thanks a lot for clarifying this to me! I've been searching in vain for operators to use, now I understand why 🙂

However, I think I have to search for a different solution though, as there can be up to 98 "kommuner" with different words in front of (or after) "kommune", and how exactly that will appear as "NetworkLocation", I won't know until they actually appear, and then it's a manual operation to include them, which won't work.

Although there are only 5 "regions", they could appear with different spelling as well. At present, I don't know how they appear in "NetworkLocation".

Do you know of any possibility of creating a custom column that for example will list an "1", if the equivalent row in the "NetworkLocation" column contains "kommune" with words either before or after "kommune"? And "0" if not?

Thank you in advance!

Kind regards,

Marianne

Hi Marianne,

You can try Text.Contains formula:

=if Text.Contains([ColumnName], "kommune") then 1 else 0

You can also use multiple nested if statements if needed:

=if Text.Contains([ColumnName], "kommune") then 1 else if Text.Contains([ColumnName], "region") then 1 else 0

Hi Marianne,

Just to be clear, Catalin is referring to the Text.Contains function in Power Query, not Power Pivot.

Mynda

oops, my mistake.

In DAX the formula is very similar to an excel formula:

NewColumn = IF(ISERROR(SEARCH("komunne", TableName[ColumnName])),0,1)

Thank you both! I figured out that the Text.Contains only worked in Power Query, and tried various lookalike solutions for Power Pivot.

But the one above just nailed it! Now I will just make nested SEARCHes and I'm done.

Thanks a lot,

Marianne