Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
If i have a table and one column will be the Employee name and another will be Product and the third the customer. For context we can say that the table records sales.
example:
Employee Product Customer
tom 1 1
tom 2 1
tom 3 2
tom 3 2
I want to get a distinct count of each of the products sold by Tom for each customer and total them.
So from the example above:
Tom has sold 3 different types of products across two different customers. The results I would be looking to produce is 3, as he sold Product 3 (twice) and product 1 and 2. Total he has 3 distinct products sold.
When I use the formula to do this I would like it to total all values for each employee until filtered (by employee, then it would show the employees number of distinct products sold).
How would I calculate this in a dax expression?
I'm sorry if this is very confusing, any help is appreciated and kudos will be giving instantly upon a correct answer. Thank you
Solved! Go to Solution.
This solution uses a star schema:
Create measure:
Distinct Product Count =
SUMX ( Employees, CALCULATE ( DISTINCTCOUNT ( Sales[Product] ) ) )
I added additional data to demonstrate that totals work. In a matrix, use Employees[Employee] as matrix rows.
Proud to be a Super User!
You can create a calculated table which will enable you to create a star schema:
Employees = DISTINCT ( Sales[Employee] )
Proud to be a Super User!
This solution uses a star schema:
Create measure:
Distinct Product Count =
SUMX ( Employees, CALCULATE ( DISTINCTCOUNT ( Sales[Product] ) ) )
I added additional data to demonstrate that totals work. In a matrix, use Employees[Employee] as matrix rows.
Proud to be a Super User!
I can't arrange my data into a star schema. Both the product and employees columns are in the same table. Do you still think this is possible?
You can create a calculated table which will enable you to create a star schema:
Employees = DISTINCT ( Sales[Employee] )
Proud to be a Super User!
Thank you so much. This really helped me. Have a great day 🙂
You're welcome! Glad to hear it worked. 🙂
Proud to be a Super User!
Just working through some changes to my db. Once that's done and I have seperate tables I'll try this method and accept as solution once i've seen it working. Thank you so much for your time!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!