Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |