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

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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors