cancel
Showing results 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

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

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors