Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 @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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |