Inactive relationship

Anything related to PowerPivot and DAX Formuale
maccaronda
Posts: 14
Joined: Tue Aug 09, 2016 7:18 am

Inactive relationship

Postby maccaronda » Thu Jan 25, 2018 2:34 am

Hi there

I currently have a fairly standard model, with invoices as the data table, and various lookup tables (calendar/projects etc).

I am trying to decide what to do about one of the tables, purchase orders. I can connect it to the invoices table, however it is ambiguous. Therefore, I tried to run a formula in order to achieve my goal which is "what is the total value of the amount invoiced against each purchase order":

Total Invoiced Purchase Order = CALCULATE(sum(Invoices[Value]),USERELATIONSHIP(PurchaseOrders[Name],Invoices[PurchaseOrder]))

Unfortunately the formula is summing just invoices value and not taking into account the amounts invoiced against that purchase order. I assume this is because the relationship is inactive.

Question, can I create a merged query that would combine the purchase order and invoices table and would this solve the problem?

And if not, what DAX formula might be better than the one I'm using attached?

Thanks
Attachments
Table.docx
(25.96 KiB) Downloaded 44 times
Model.docx
(100.69 KiB) Downloaded 43 times

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: okgo and 2 guests