Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

Getting a Count of data for each Employee (Need Dax help)

 

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

2 ACCEPTED SOLUTIONS
DataInsights
Super User
Super User

@AndrewDavies437,

 

This solution uses a star schema:

 

DataInsights_0-1653240548232.png

 

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.

 

DataInsights_1-1653240610390.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@AndrewDavies437,

 

You can create a calculated table which will enable you to create a star schema:

 

Employees = DISTINCT ( Sales[Employee] )

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
DataInsights
Super User
Super User

@AndrewDavies437,

 

This solution uses a star schema:

 

DataInsights_0-1653240548232.png

 

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.

 

DataInsights_1-1653240610390.png

 





Did I answer your question? Mark my post as a solution!

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?

@AndrewDavies437,

 

You can create a calculated table which will enable you to create a star schema:

 

Employees = DISTINCT ( Sales[Employee] )

 





Did I answer your question? Mark my post as a solution!

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. 🙂





Did I answer your question? Mark my post as a solution!

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.