Count of Distinct Subcategory Occurence

[Anonymous]

Hi All,

I am trying to set up a measure that only counts the total subcategory count of items that have been ordered. I have two key tables and a fact table

Category Key Table

Category	Category Key
Accessory	1
Bike	2
Clothing	3

 Subcategory Key Table

Category	Subcategory	Subcategory Key	Category Key
Accessory	Helmet	1	1
Accessory	Bottle	2	1
Accessory	Bike Rack	3	1
Bike	Wheel	4	2
Bike	Chains	5	2
Clothing	Gloves	6	3
Clothing	Jersey	7	3

Order #	Category	Category Key	Subcategory	Subcategory Key	Ordered (Y/N)
44455	Accessory	1	Helmet	1	Y
44455	Accessory	1	Bike Rack	3	Y
44455	Accessory	1	Wheel	4	Y
22233	Bike	2	Chains	5	Y
22233	Bike	2	Wheel	4	Y
22233	Clothing	3	Gloves	6	Y
11199	Clothing	3	Wheel	4	Y
11199	Clothing	3	Jersey	7	Y

The data is taken from an excel tracker which looks like the above fact table when transformed.

If I were to create a count of the subcategory (total items ordered) I would return 8, which is incorrect. The category key, subcategory key, and master fact table are all linked. I would only want to return a count of those subcategory items that are correctly associated in the subcategory key table to the correct category key, which should give me a count of 6 total items based off of the master table. 

Currently, my DAX is set up to be: Total Subcategory Count = Calculate(Count(Order ID), [Ordered] = "Y")

Is there some way for the DAX to be set up where it only counts the subcategory key if it's correctly associated with the right category key?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try:

Total Subcategory Count = CALCULATE(DISTINCTCOUNT('Table'[Subcategory Key]),'Table'[Ordered (Y/N)] = "Y")

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum