switch SEARCH

[NickProp28]

Dear community, 

I have customer codes concatenated into one and form customer list, now I would like to determine if the list will have these customers.

'AAA' , 'BBB', 'CAC' ,'CCC' , 'AEW', 'ABC', 'BAC' .... (more than 50). 

If yes, it will be categorized as 'NICE'. 

Category = 
SWITCH (
    TRUE (),
    SEARCH ( "AAA", Combine[Customer list], 1, 0 ) > 0, "NICE",
    SEARCH ( "BBB", Combine[Customer list], 1, 0 ) > 0, "NICE",
    "NON NICE"
)

I wonder if there is an easier way to do this, since I will have to type SEARCH more than 50 times...

Appreciate any help provided!

[Manoj_Nair]

@NickProp28 - try the PBIX file (low code). Agree with @TomMartens suggestion to use PowerQuery instead of DAX calculated column.

Please let me know if this answered your question. I would be happy if you could mark my post as a solution and give it a thumbs up

Best regards
Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

[Manoj_Nair]

@NickProp28 - the reason for using “Comparer.OrdinalIgnoreCase” to factor if in future you have distinct values ( ie BBB, AAA etc) which are case insensitive compared. For more details check Microsoft documentation.

[TomMartens · Accepted solution]

Hey @NickProp28 ,

I recommend using Power Query instead of creating a calculated column with DAX.

Here you will find a pbix file that contains an example using Power Query: https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/Ef26aLnUwbJNk4CBMmb6hzUBH5CLT...

The above solution is based on two tables, one containing all the "possible values" and one where these possible values might appear.

I consider using Power Query more appropriate as a calculated DAX column will not be as much compressed as native or Power Query columns during data load/data refresh.
Another advantage is that the table containing the possible values can easily be adapted.

Hopefully, this provides what you are looking for.

Regards,

Tom