Check if results contain specific string

[Anonymous]

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_id	1 - *	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!

[v-henryk-mstf · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[AlexisOlson]

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]

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]

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