I have a customer spend report that I have been developing. One of the needs of the report is to provide customer spend totals for our parts department. Parts can be purchased over the counter or can also be included on a service ticket. I have two ledgers in my PBIX file. General Ledger Entries PAR and Service Ledger Entries. The common key is the invoice number. The relationship between the two tables is many to many, so I created an intermediate table that I can use to look up invoice numbers from the General Ledger Entries PAR table find the matching invoice(s) in the Service Ledger Entries table and sum the amounts in that table. The issue I am having is when I use the SumX(Filter()) statement in Dax. I can’t bring up the General Ledger Entries PAR Table as a filter argument. I have created a relationship for all three tables. Not sure what I am overlooking, but I am guessing its simple. I have attached the PBIX and screenshots.
Below is the Dax statement.
Total Service Amount =
SUMX (
FILTER (
‘Invoice Numbers’,
‘Invoice Numbers’[Invoice Number] = ‘General Ledger Entries PAR’[Invoice Number]
),
‘Service Ledger Entries’[Amount]
)
Customer Spend Report 02172023 Dev.pbix (32.1 MB)