Filter problem

[setis]

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 other.

I'm looking for a measure that per Customer Nr, gives me the SUM of the [Amount w VAT] from Sales Invoice Lines of all the Document No in Sales Invoice Lines (second table), that have a match in Detailed Cust_ Ledg_ Entry (first table).

The problem I have with the following measure is that the results are not correct when I place the measure with customers on rows: 

SalesFee = 
CALCULATE (
    SUM ( 'Sales Invoice Line'[Amount w VAT] );
    FILTER (
        'Sales Invoice Line';
        'Sales Invoice Line'[Document No_]
            = CALCULATE (
                MAX ( 'Detailed Cust_ Ledg_ Entry'[Document No_] );
                FILTER (
                    'Detailed Cust_ Ledg_ Entry';
                    'Detailed Cust_ Ledg_ Entry'[Document Type] = 2
                )
            )))
  

When I place this measure with Cust_Ledger Entry No_ on rows, the results line by line are correct. But with the customers on rows, it takes the results for only one line.

I think that the problem is in the filter. The MAX that I have to put in, when placed on customers, it gives me only the results for only one line. I probably have to build the filter with IN and/or Summarize but I'm not sure how.

I have reproduced the issue on a dummy report here: https://drive.google.com/file/d/1FWfP8nCVru8DYGrucvztsu2P5jXw3LCg/view?usp=sharing

I would really appreciate any kind of help. 

[amitchandak]

I think there is something missing in the post. What is the issue.

[setis]

I'm sorry, part of my original post was deleted. (I am copying the below on the original post)

Both tables are linked to a look up customer table and I need a measure that gives me the amount from Sales Invoice Lines when the Doc type is 2 and the Document numbers on both tables is the same. 

The problem I have with the following measure is that the results are not correct when I place the measure with customers on rows: 

SalesFee = 
CALCULATE (
    SUM ( 'Sales Invoice Line'[Amount w VAT] );
    FILTER (
        'Sales Invoice Line';
        'Sales Invoice Line'[Document No_]
            = CALCULATE (
                MAX ( 'Detailed Cust_ Ledg_ Entry'[Document No_] );
                FILTER (
                    'Detailed Cust_ Ledg_ Entry';
                    'Detailed Cust_ Ledg_ Entry'[Document Type] = 2
                )
            )))
  

I think that the problem is the MAX that I have to put in the filter that when placed on customers, it gives me only the results for one line. I probably have to build the filter with IN and/or Summarize but I'm not sure how

[setis]

Dear @amitchandak and @VasTg ,

Thanks for letting me know that the original post was not completed. 

I have reproduced the issue here. https://drive.google.com/file/d/1FWfP8nCVru8DYGrucvztsu2P5jXw3LCg/view?usp=sharing 

Thank you very much in advance!

[setis]

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

[Anonymous · Accepted solution]

@setis 

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

[VasTg]

@setis 

What have you tried and what is the problem?