Help with measure

[Anonymous]

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

[v-xiaotang]

Hi @Anonymous 

In addition to CONTAINS, you can also use IN, for example

Measure = COUNTROWS(FILTER('Table A', 'Table A'[Product] in VALUES('Table B'[Product])))

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.

[m_alireza · Accepted solution]

Hi @Anonymous ,

Try this measure: 

Measure = COUNTROWS(FILTER('Table A',CONTAINS('Table B',[Product],'Table A'[Product])))

[Anonymous]

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.

[Anonymous]

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!

[m_alireza]

Hi @Anonymous ,

Alternatively, you can try this measure: 

Measure = CALCULATE(COUNTROWS(FILTER('Table A',CONTAINS('Table B',[Product],'Table A'[Product]))),'Table A'[ProduceType]="Organic")