Count of child records for each parent record.

[bschmiedeler]

I have two tables in my model. The first is a list of businesses with a primary key, BussinessID. The second is a list of completed trips which includes a BusinessID.  So this is obviously a one to many child relationship.

All I want to know how to do is to add a calaculated column in the parent table that would give me the sum of all trips for a particular BusinessID.

This should be easy, but I cannot figure it out.

[v-gizhi-msft · Accepted solution]

Hi,

You can create a calcutaled column as below:

Column = CALCULATE(SUM(CompletedTrips[Trips]),FILTER(CompletedTrips,CompletedTrips[BusinessID]=Business[BusinessID]))

The result shows:

And i advise you using measure to meet your requirement:

Measure = SUM(CompletedTrips[Trips])

The result shows the same with above:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[amitchandak]

Try a new column in pbibusiness

All Trips = countX(filter(pbiCompletedTripbyBusiness,piCompletedTripbyBusiness[Business_id] =pbibusiness[piCompletedTripbyBusiness]),pbiCompletedTripbyBusiness[trip_id])


All Trips = Sumx(filter(pbiCompletedTripbyBusiness,piCompletedTripbyBusiness[Business_id] =pbibusiness[piCompletedTripbyBusiness]),pbiCompletedTripbyBusiness[trips])

[Greg_Deckler]

I think:

COUNTROWS(RELATEDTABLE('ChildTable'))

?