Sum rows in parent table with no related rows in child table

[Anonymous]

Hi all,

I have a strange situation/model where the header has some sales ID records and amounts which have no corresponding lines in the child/many side. I need to sum all the values from the header where there are no matching lines. This is a one to many relationship between header and lines.

Example:

Sales Header (parent)

Sales Key                Sales Amount

1                                   500

2                                   300

3                                   100

4                                   200

Sales Lines

Sales Key  Line#    

1                  1           

1                  2       

2                  1

What I'd need is the sum amount from the header rows for Sales Key 3 and 4 in the above example, which would return 300. If we did a left outer join it would be the null rows.

Essentially I need to CALCULATE(SUM(SalesHeader[Amount]), FILTER(SalesHeader, SalesHeader[Sales Key]

NOT IN (SalesLines[Sales Key])

Obviosuly the above is incorrect as I'm not sure how to express NOT IN [list of values in related table] by using DAX. Any help is wonderful!

[Cmcmahan]

NOT is available in DAX though. The syntax would look like this:

 CALCULATE(SUM(SalesHeader[Amount]), FILTER(SalesHeader, NOT(SalesHeader[Sales Key] IN SalesLines[Sales Key])))

[Anonymous]

This doesn't seem to work, unfortunately. 

After the IN statement, I cannot specify a column name, it's looking for a measure or table for some reason.

[Cmcmahan · Accepted solution]

Then use VALUES(SalesLines[Sales Key]) to create a table of values for it to use.

[Anonymous]

Ah, of course. Worked like a charm, thank you!