Sum values which are not in another table

[Sebx]

Hello,

I have datamodel with two tables - table with sales (id, amount) and second table with incorrect payments (id, reason). Tables are connected by relationship on Id in both tables (in other words sales table contains all ids and incorrect payments has some ids from sales). I want to create sum of amounts from sales table, with excluded payments from incorrect payments table.

I know that I could make join in powerquery, but I would like to use measure instead. I have tried something like below, but it didn't work. Any ideas?

CALCULATE(SUM('sales'[amount]),ISBLANK('incorrect payments'[Id]))

Thank you

[Anonymous · Accepted solution]

I made 2 dummy tables to replicate the story you have provided. This essentially lets you search sum the Sales[Amount] by filtering out rows where the Sales ID exists in the Incorrect Payments table.

Sales

Sales ID	Amount
1	$50
2	$100
3	$150
4	$100
5	$250
6	$100
7	$150
8	$110
9	$125
10	$108

Incorrect Payment

Sales ID	Reason
4	Declined Payment
8	Invalid Payment Method
9	Declined Payment
10	Duplicate Payment

Sales Excluding Failed Payment = 
VAR _VariableTable =
    CALCULATETABLE (
        VALUES ( 'Incorrect Payments'[Sales ID] ),
        ALLSELECTED ( 'Incorrect Payments' )
    )
    
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( Sales ), NOT ( Sales[Sales ID] ) IN _VariableTable ),
        Sales[Amount]
    )

[Sebx]

That's exactly what I needed! Thank you so much!