Forum Discussion

SevsBo's avatar
SevsBo
Responsive Resident
1 year ago
Solved

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"))  

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.