Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
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
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 |
You sure you're doing it as described? I get exactly the results you show in your first post
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.
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?
User | Count |
---|---|
131 | |
74 | |
70 | |
58 | |
53 |
User | Count |
---|---|
190 | |
97 | |
67 | |
62 | |
54 |