Forum Discussion
switch SEARCH
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!
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/Ef26aLnUwbJNk4CBMmb6hzUBH5CLTgk7US0Ld3bNPBDZpQ?e=cnKUEX
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
3 Replies
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/Ef26aLnUwbJNk4CBMmb6hzUBH5CLTgk7US0Ld3bNPBDZpQ?e=cnKUEX
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
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/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.
