Calculate Sum of table1 column1 based on table 1 column 2 and table 2 column 2 or not equal

Brains86 — Thu, 16 Sep 2021 14:47:36 GMT

I have 3 tables:

1. company (company_id, name, adres, etc..)
2. quotes (quotes_id, company_id, value, project_number etc..)
3. orders (orders_id, company_id, value, project_number)

The tables are 1 to many connectect through company_id

Table 2 quotes:

quotes_id	company_id	value	project_number
1	55	100	20201
2	55	10	20202
3	57	2000	20203
4	58	20	20204

Table 3 orders:

orders_id	company_id	value	project_number
1	55	100	20201
2	57	2000	20203
3	53	25	20208
4	58	300	20209

I want to formulate a DAX formule which counts the value of de orders (order[value]) but only from project number (orders[project_number]) that don't exist in de quotes table (quotes[project_number])

So in this case the result should be 25 + 300 = 325.
please help.

Re: Calculate Sum of table1 column1 based on table 1 column 2 and table 2 column 2 or not equal

BeaBF — Thu, 16 Sep 2021 15:22:08 GMT

First thing: create a calculated column in the Order table as follows:

1) BOOLEAN = IF(LOOKUPVALUE('Quotes'[project_number], 'Quotes'[project_number], 'Orders'[project_number])=BLANK(), 0, 1)

Then create a measure as follows:

2) Misura = CALCULATE(SUM('Orders'[value]), 'Orders'[BOOLEAN] = 0)

I hope it is for you!

topic Calculate Sum of table1 column1 based on table 1 column 2 and table 2 column 2 or not equal in DAX Commands and Tips

Calculate Sum of table1 column1 based on table 1 column 2 and table 2 column 2 or not equal

Re: Calculate Sum of table1 column1 based on table 1 column 2 and table 2 column 2 or not equal