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

Question

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 · Answer

Hi&nbsp;Anonymous&nbsp;
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] ) )
    )
)

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

anonymous · Answer

Hello&nbsp;AlB&nbsp;I tried with the frutis scenario and it worked but in production it didnt work.&nbsp;I have the following DAX, the only differece with yours are the ";" which were not recognized and I changed them to ","&nbsp;&nbsp;CountProducto = 
COUNTROWS (
    FILTER (
        visitas,
        CONTAINSSTRING (visitas[Product],SELECTEDVALUE(UCVisitas_ProductoServicio[Title]))
    )
)&nbsp;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.&nbsp;What I noticed is the result in production also gives me an empty "Category" (A).&nbsp;Thank you for your help.&nbsp;

anonymous · Answer

Hello&nbsp;AlB&nbsp; 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)&nbsp;the output is showing:Apple1Banana1Orange1Grape1&nbsp;&nbsp;&nbsp;&nbsp;

alb · Answer

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

alb · Answer

&nbsp;
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?&nbsp;

Forum Discussion

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

6 Replies

Related content

Filter depending on another filter

Slicer Filtering Another slicer

Contain Word

check if value contains in another table

Filter contains