Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |