cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

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
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!

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!

6 REPLIES 6
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!

Helper I

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?

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!

Helper I

Thank you so much. This really helped me. Have a great day 🙂

Super User

You're welcome! Glad to hear it worked. 🙂

Proud to be a Super User!

Helper I

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!

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Exclusive opportunity for Women!

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!

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors