Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
m_alireza
Solution Specialist
Solution Specialist

Hi @Anonymous ,

Try this measure: 

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

View solution in original post

5 REPLIES 5
v-xiaotang
Community Support
Community Support

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])))vxiaotang_0-1668579502686.png

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
Solution Specialist
Solution Specialist

Hi @Anonymous ,

Try this measure: 

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

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
Not applicable

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: 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.