Not finding Related table or column - Sharepoint List data source

[joemillson]

I have 2 tables, both from Sharepoint Lists. One is a Projects table, the other an Invoices table. 

I've related the Project ID field from Invoices back to the Project ID PK in the Projects table - this is a many-to-one relationship.

I need to be able to sum up the total value of the invoices related to a project (ie one row in the Project table).

I've tried to do this by adding a new custom column in Projects and using RELATED(), however it's not finding the Invoices table (and therefore I can't select the column for Amount)

Any help would be appreciated

Joe

[v-shex-msft · Accepted solution]

Hi @joemillson,

You can try to write a measure at Project table side:

Use relationship to calculate:
Total Value=
SUMX(Filter(ALL(Invoices),Invoices[Project ID]= Related(Project[Project ID])),Invoices[Value])

Use current project id to calculate:
Total Value=
Var currID =LASTNOBLANK(Project[Project ID],[Project ID])
return
SUMX(Filter(ALL(Invoices),Invoices[Project ID]= currID),Invoices[Value])

In addition, you can check you relationship setting if the "cross filter direction" option not set to "both".

Regards,

Xiaoxin Sheng