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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.