Forum Discussion
Solved
Filter problem
Dear all, I have 2 tables: Detailed Cust_ Ledg_ Entry and Sales Invoice Line Both tables are linked to a look up customer table. But there is not relationship between each...
I think you are linking the tables in a right way for calculation, try create a relationship for the two tables and alter a bit.
And your formula should work after remove "{ }":
Measure = CALCULATE( SUM('Sales Invoice Line'[Amount w VAT]), FILTER( 'Sales Invoice Line', 'Sales Invoice Line'[Document No_] in VALUES('Detailed Cust_Ledg_entry'[Document No_])), FILTER('Detailed Cust_Ledg_entry','Detailed Cust_Ledg_entry'[Document Type]=2))
Best regardsPaul Zheng
My last attempt of solving this using IN in the filter is:
CALCULATE (
SUM('Sales Invoice Line'[Amount w VAT]);
FILTER (
'Sales Invoice Line';
'Sales Invoice Line'[Document No_]
IN {
VALUES('Detailed Cust_ Ledg_ Entry'[Document No_])
}
);
FILTER (
'Detailed Cust_ Ledg_ Entry';
'Detailed Cust_ Ledg_ Entry'[Document Type] = 2
))
But I'm getting the "multiple values" error.
The problem again is that there are duplicates in Document No_ in both tables. Any ideas? Thanks again
I think you are linking the tables in a right way for calculation, try create a relationship for the two tables and alter a bit.
And your formula should work after remove "{ }":
Measure =
CALCULATE(
SUM('Sales Invoice Line'[Amount w VAT]),
FILTER(
'Sales Invoice Line',
'Sales Invoice Line'[Document No_] in VALUES('Detailed Cust_Ledg_entry'[Document No_])),
FILTER('Detailed Cust_Ledg_entry','Detailed Cust_Ledg_entry'[Document Type]=2))
Best regards
Paul Zheng