DAX Count Filtering where a column contains text from another Table's Column

[Anonymous]

Hello I need to create a meassure that counts the items in a column filtering if the column contains a text from another table's column, I'll explain.

Table1:

ID	Product
1	Apple, Banana, Orange
2	Apple
4	Banana
5	Orange
6	Banana, Orange
7	Apple, Grape
8	Grape
9	Banana, Orange, Grape
10	Orange, Grape

Table2: (Fruits Catalog)

Fruits

Apple

Banana

Orange

Grape

What I need is for each Table2[Fruits] count the Table1[ID] when Table1[Product] contains Table2[Fruits].

Expected outcome:

Apple	3
Banana	4
Orange	5
Grape	4

Hope you can help me, I have tried with the contains and containsstring with no posisitve results.

Thank you

[AlB]

Hi @Anonymous 

Try this:

1. Place Table2[Fruit] in the rows of a matrix visual

2. Create this measure and palce it in the visual

Measure =
COUNTROWS (
    FILTER (
        Table1;
        CONTAINSSTRING ( Table1[Product]; SELECTEDVALUE ( Table2[Fruits] ) )
    )
)

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Anonymous]

Hello @AlB  I tried as you instructed but it is only showing the individual values, so it is not counting the fields where I have mixed fruits (cases..1, 6, 7, 9,10)

the output is showing:

Apple	1
Banana	1
Orange	1
Grape	1

[AlB]

You sure you're doing it as described? I get exactly the results you show in your first post 

[Anonymous]

Hello @AlB 

I tried with the frutis scenario and it worked but in production it didnt work.

I have the following DAX, the only differece with yours are the ";" which were not recognized and I changed them to ","

CountProducto = 
COUNTROWS (
    FILTER (
        visitas,
        CONTAINSSTRING (visitas[Product],SELECTEDVALUE(UCVisitas_ProductoServicio[Title]))
    )
)

Here is a pic of the info that is not working correctly:

A = The result, as you can see, there es an empty "product".

B = The count of the products (without the filter) where you can see an example of the mixed products (line 3).

C = The products catalog.

What I noticed is the result in production also gives me an empty "Category" (A).

Thank you for your help.

[riop]

did you find a solution for this? Having the same issue.

[AlB]

The empty lines frequently show up as a result of an item in the dim table that does not exist int he fact table. However, I cannot tell what's going on without more info. Can you share the pbix?