How to find the sum of values present in one table and missing in other table

Question

I am trying to find the sum of the values corresponding to a key that is present in Table 1 but not in Table 2.

These tables have been created based on some filters and represent the values on 2 different dates.

The two different dates are chosen from 2 different date tables which have an inactive relationship, specially created for this purpose.

I want to create a measure that finds out the sum.

Below is the syntax I have used:

 Difference = 
VAR 
Table1 = CALCULATETABLE(VALUES('TableA'[Id]), 'TableA'[Type] = "ABC", ALL('Date'), USERELATIONSHIP('Date'[As of Date], Previous_Date[Previous_Date]),USERELATIONSHIP('Date'[As of Date], 'TableA'[As of Date]))
VAR
Table2 = CALCULATETABLE(VALUES('TableA'[Id]), 'TableA'[Type] = "ABC", USERELATIONSHIP('Date'[As of Date], 'TableA'[As of Date]))
RETURN
IF(AND(VALUES('TableA'[Id]) IN Table1 , NOT(VALUES('TableA'[Id])) IN Table2), 
CALCULATE(SUM('TableA'[Values])),0)

It is error-free. However, when I drop the measure on a KPI visual, I am getting the following message:

Please tell me what is wrong with the syntax. Also, please let me know if there is any better code that can be written.

Kindly help.

Thanks for the reply. I am somewhat new to stack overflow and don't know how I can. Please tell me how I would be able to upload it. — Ameya Bhave
You can simply upload the file to dropbox and provide a link — StelioK

Alexis Olson Alexis Olson · Accepted Answer · 2019-06-28T15:47:52

The reason you are getting an error is that it expects a single value for x when you write x IN Table1 but using the VALUES function can return a list rather than a single value.

I'd try something more like this after the RETURN:

SUMX (
    'TableA',
    IF ( 'TableA'[Id] IN Table1 && NOT ( 'TableA'[Id] IN Table2 ), 'TableA'[Value] )
)

This iterates through each row of TableA and checks if the Id value in each row is in the tables you calculated. If the condition is met, it adds the Value. Otherwise IF returns a blank (which is treated the same as 0 in a sum) since there is no third argument.

How to find the sum of values present in one table and missing in other table

1 Answers