topic Count of Subcategory Rows related to another table in DAX Commands and Tips

Count of Subcategory Rows related to another table

adam_macs — Mon, 15 Aug 2022 12:54:25 GMT

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.

Re: Count of Subcategory Rows related to another table

jplouw1 — Mon, 15 Aug 2022 11:36:00 GMT

Please share a screenshot of your Data model

Re: Count of Subcategory Rows related to another table

adam_macs — Mon, 15 Aug 2022 11:40:02 GMT

Re: Count of Subcategory Rows related to another table

Anonymous — Thu, 18 Aug 2022 09:08:52 GMT

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.