cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
valcat27
Helper III
Helper III

Count of distinct values by ID based on another table

Hello all,

 

I have two tables: Sales and Store tables. Both have multiple columns and they are related by the "StoreID" column. 

Below you can see a sample of that. 

 

Sales Table

valcat27_4-1614621274710.png

 

Store Table

valcat27_1-1614621145735.png

 

I want to create a new column in the Store table with the number of products by store, based on Sales table.  The "ProductID" column can have nulls and duplicate values, so I think the most suitable count is distinctcount. 

 

This is what I want:

valcat27_3-1614621210910.png

 

Can anyone help me writing the right formula?

 

Thanks in advance. 

 

1 ACCEPTED SOLUTION
AlexisOlson
Memorable Member
Memorable Member

Given the relationship, I think you can just write

Product_count = CALCULATE ( DISTINCTCOUNT ( Sales[ProductID ) )

View solution in original post

2 REPLIES 2
AlexisOlson
Memorable Member
Memorable Member

Given the relationship, I think you can just write

Product_count = CALCULATE ( DISTINCTCOUNT ( Sales[ProductID ) )

View solution in original post

Hello @AlexisOlson ,

 

Thank you for your help! It works.

I had selected a wrong table with a similar name and that's why it was not working. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors