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.

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