Count of distinct values by ID based on another table

[valcat27]

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

Store Table

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:

Can anyone help me writing the right formula?

Thanks in advance. 

[AlexisOlson · Accepted solution]

Given the relationship, I think you can just write

Product_count = CALCULATE ( DISTINCTCOUNT ( Sales[ProductID ) )

[valcat27]

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.