Count of Subcategory Rows related to another table

[adam_macs]

Hi, I have 2 tables that are connected via the Customer ID.

My aim is to have a distinct count of the total customers within a given subcategory of Table B if one or more customers from Table A are found within that subcategory.

This would tell me how much of the market we have penetrated. For example, we sell road bikes to Jon Yang which is 1 out of a possible 6 customers within the road bike category.

So essentially, if we have customers from Table A in Table B, what is the total number of customers within the subcategories they exist in.

Any help much appreciated.

[Anonymous · Accepted solution]

Hi @adam_macs ,

I do the test and this is my data of two tables:

I got this result through my testing:

and through filtering, you can get the count of rows related to each subcategory.

for example, I choose Bike , it get the correct result:

so if this result can meets your needs, you can try this measure:

Measure = COUNTROWS(FILTER('Table 1','Table 1'[customer id] in VALUES('Table 2'[customer id])))

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jplouw1]

Please share a screenshot of your Data model

[adam_macs]