Forum Discussion

Panta's avatar
Panta
New Member
5 years ago
Solved

DAX measure - Distinct count referencing second table

I have two tables in my model: Sales table and Products table.

 

Sales table contains columns: Year; Customer_name; Product_Name; Sales_Amount.
Product table contains columns: Product_Name; Brand_Name

 

I need to create two measures to show:

1. Count of distinct products purchased by a customer in a given year
2. Count of distinct Brands purchased by a customer in a given year.

 

I did task 1 with the following function:
ProductCount_2020 = CALCULATE(DISTINCTCOUNTNOBLANK(Sales[Product_Name]); 'Calendar'[Year] = 2020)

 

I need help for the second task: how to get the count of distinct brands purchased by a customer on the basis of distinct products purchased? (i.e. how to reference the second table that relates products to theri brands?)

 

Thank you

2 Replies