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.
Hi Gurus, I am trying to create a measure which counts the values from table A when the same value is found in table B. Table A has other values but we only count the values, which are part of table B.
For ex.
Table A
OrderId Product
1 Orange
2 Apple
3 Mango
4 Brush
5 Orange
6 Spoon
Table B
Orange
Mango
Pineapple
So, the result for my measure will be 3 becasuse table A has 2 entries of Orange and 1 entry of mango. Hence 3
Appreciate if you can provide your expertise on this.
Thanks,
Nama
Solved! Go to Solution.
Hi @Anonymous ,
Try this measure:
Measure = COUNTROWS(FILTER('Table A',CONTAINS('Table B',[Product],'Table A'[Product])))
Hi @Anonymous
In addition to CONTAINS, you can also use IN, for example
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Try this measure:
Measure = COUNTROWS(FILTER('Table A',CONTAINS('Table B',[Product],'Table A'[Product])))
Hi @m_alireza This worked like a charm. However I have small enhancement to this requirement. I want to put a condition on Table A so that only those records are counted. Ex. Assuming my Table A has an additional column called ProduceType(with values Organic and Non-Organic) and I want to count records in Table A where ProduceType = 'Organic' and those products exists in Table B.
I managed to figure this out :
Measure = COUNTROWS(FILTER(FILTER('Table A', 'Table A'[Produce Type] = "Organic"), CONTAINS('Table B',[Product],'Table A'[Product])))
Please let me know if there is a better way than this or you find any issue with it.
Thanks again!
Hi @Anonymous ,
Alternatively, you can try this measure:
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |