Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help with measure

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

Need Help
  • m_alireza's avatar
    m_alireza
    3 years ago

    Hi Anonymous ,

    Try this measure: 

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

5 Replies

  • m_alireza's avatar
    m_alireza
    Solution Specialist
    3 years ago

    Hi Anonymous ,

    Try this measure: 

    Measure = COUNTROWS(FILTER('Table A',CONTAINS('Table B',[Product],'Table A'[Product])))
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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!

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    3 years ago

    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.