Column with number of related records

[lowcodelewis]

Hi,

I have 2 tables. One with document locations (parent), one with documents (child). There is a 1:N relationship between these tables where a single document location can have many documents related to it. 

On my table of document locations, I want to be able to have a column which has a count of the number of related documents to that record. On my document locations table, there is a table column for documents as a result of the relationship between these Dataverse tables.

I am now trying to use a custom column with the following formula to produce the column. This works, but then because I think I'm almost doing a 'For All' type operation for every document location record, the number of API calls is dramatically high and things run incredibly slow and eventually fall over and the data just doesn't completely load at all.

This is the formula I tried in my custom column.

Table.RowCount([lcl_document])

Can anyone suggest the correct way to do this so that things dont slow down and just stop working?

Thank you in advance Power BI friends!

Lewis Baybutt

Microsoft Business Applications MVP

lowcodelewis.com

[lowcodelewis · Accepted solution]

Thank you Sahir! I did wonder about doing things like this but then thought that it might be easier to go with a more direct approach. I've managed to achieve this and have blogged here:

Show a count of related records in Power BI - Low Code Lewis

The only difficulty is that the visual won't display records with 0 documents. So I may well have to go with the approach of producing a table of data with counts in Power Automate and make the counts strings to show this data.

Thank you for your help! 

[Sahir_Maharaj]

This way, you can simply retrieve the pre-calculated count from the table instead of recalculating it every time the table is loaded, reducing the number of API calls and improving the performance.

[Sahir_Maharaj]

Hello @lowcodelewis,

One way to reduce the number of API calls and improve the performance is to use Power Automate to pre-calculate the number of related documents for each document location and store it in the document locations table.

You can set up a flow that runs on a schedule (e.g. daily) to update the count of related documents for each record.