Forum Discussion
Containsstringexact or containsstring but NOT substring, only whole selected word?
I'm having an issue where DAX for eiher Containsstring or Containsstringexact is returning partial matches. I know this is by design but I am wondering if there is a way to only match for specific word, not partial matches, or would I have to specifically omit the words I do not want captured?
As an example:
containstring (..., "Red Box") ||
containstring (..., "Green Box")
I would only want to show words that are either "Red Box" or "Green Box", but not "Large Red Box" or "Gold Plated Green Box".
Would I have to do something like this for each one I want to omit or is there a better DAX expression to only get requested results?
containstring (..., "Red Box") && NOT (containstring (..., "Large Red Box")) ||
containstring (..., "Green Box") && NOT (containstring (..., "Gold Plated Green Box"))
Thanks for bhanu_gautam's concern about this issue.
Hi, SevsBo
Since you didn't provide me with some test data, I assumed some data myself for testing:
You can then create a Calculated column to determine the original name if it is Red Box or Green Box, and null if it is not:
ExactMatch = IF ( ([Product Name] = "Red Box") || ([Product Name] = "Green Box"), [Product Name], BLANK() )Then create a Measure:
ExactMatchMeasure = COUNTAX ( FILTER ( 'Table', NOT(ISBLANK([ExactMatch])) ), [ExactMatch] )Finally drag the created Measure to the Filters panel to filter the data:
I have attached the pbix file for this simple example below, I hope it helps.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
SevsBo , Try using
VAR RedBox = "Red Box"
VAR GreenBox = "Green Box"RETURN
IF (
CONTAINSSTRING([YourColumn], RedBox) &&
(
LEFT([YourColumn], LEN(RedBox)) = RedBox &&
(LEN([YourColumn]) = LEN(RedBox) OR MID([YourColumn], LEN(RedBox) + 1, 1) = " ")
) ||
CONTAINSSTRING([YourColumn], GreenBox) &&
(
LEFT([YourColumn], LEN(GreenBox)) = GreenBox &&
(LEN([YourColumn]) = LEN(GreenBox) OR MID([YourColumn], LEN(GreenBox) + 1, 1) = " ")
),
TRUE,
FALSE
)Thanks for bhanu_gautam's concern about this issue.
Hi, SevsBo
Since you didn't provide me with some test data, I assumed some data myself for testing:
You can then create a Calculated column to determine the original name if it is Red Box or Green Box, and null if it is not:
ExactMatch = IF ( ([Product Name] = "Red Box") || ([Product Name] = "Green Box"), [Product Name], BLANK() )Then create a Measure:
ExactMatchMeasure = COUNTAX ( FILTER ( 'Table', NOT(ISBLANK([ExactMatch])) ), [ExactMatch] )Finally drag the created Measure to the Filters panel to filter the data:
I have attached the pbix file for this simple example below, I hope it helps.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
