Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Check if results contain specific string

I think this might be a basic question but I want to create a measure that will let me know if my results contain a specifc string. My data is set up like the below:

Table 1: Orders
order_id

Table 2: Products
order_id
product_type

Orders Products
order_id1 - *product_type


I created a table visual that lists all my orders. In another column I wanted to add a measure that could tell me TRUE, FALSE if my order ID contains a specific product which I would specifiy with a string - "Baseball". There are only about 10 different product types overall. 

I tried: 
Measure  = containsstring((concatenatex(Products,Products[product_type],",")),"Baseball")

It is just way to slow however and I end up exhausing the resources. 

Thanks in advance for any suggestions on how to solve this one!

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi Anonymous ,

     

    According to your needs, I did the following tests:

    M = 
IF ( CONTAINSSTRING ( MAX ( 'Table'[Products] ), "Baseball" ), "True", "False" )

     

     


    If the problem is still not resolved, please provide a detailed data model (delete sensitive information) or expected results. I will answer for you as soon as possible. Looking forward to your reply.



    Best Regards,
    Henry

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

    This is an inefficient way to do things because you are concatenating the product type in every single row of Products.

     

    A more efficient way is to look at only the different product types in the current filter context and check if any one of those few values contains the string. If none of them do, you get an empty table in the measure below:

     

    Measure =
NOT (
    ISEMPTY (
        FILTER (
            VALUES ( Products[product_type] ),
            CONTAINSSTRING ( Products[product_type], "Baseball" )
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks so much Alexis! I this isn't something I thought of and I think this solution would work - however I am still running into resource issues with this. Perhaps there is just too much data to achieve what I want with a measure alone. 

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        5 years ago

        If you can create a calculated column in your model, that would be more efficient than having to use a measure.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi Anonymous ,

     

    According to your needs, I did the following tests:

    M = 
IF ( CONTAINSSTRING ( MAX ( 'Table'[Products] ), "Baseball" ), "True", "False" )

     

     


    If the problem is still not resolved, please provide a detailed data model (delete sensitive information) or expected results. I will answer for you as soon as possible. Looking forward to your reply.



    Best Regards,
    Henry

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.