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

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

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors