Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
epolin
Frequent Visitor

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

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:

IDProduct
1Apple, Banana, Orange
2Apple
4Banana
5Orange
6Banana, Orange
7Apple, Grape
8Grape
9Banana, Orange, Grape
10Orange, 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:

Apple3
Banana4
Orange5
Grape4

 

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

 

Thank you

6 REPLIES 6
AlB
Super User
Super User

Hi @epolin 

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  Datanaut

epolin
Frequent Visitor

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:

Apple1
Banana1
Orange1
Grape1

 

 

 

 

 

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

epolin
Frequent Visitor

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:

abc.jpg

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.

 

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

 

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? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.