Select rows from table 2 that are not in table 1

[lburgess]

I've been beating my head against the wall on this one for several days.  Hoping someone here can help.

I'm building a report against an onboarding application.  There are two primary lists involved.  "Onboarding Tasks" is a SPO list of all tasks involved in onboarding a new branch.  "Branch Onboarding Tasks" is a list of all tasks that have been scheduled for a specific branch.  New tasks are scheduled as parent tasks are completed, so the Branch Onboarding Tasks doesn't always have all of the tasks listed.  I'm trying to build a table in my report that contains all tasks from Onboarding Tasks that have not been scheduled for a given branch (i.e. the tasks don't exist in Branch Onboarding Tasks for that particular branch).

Sample data:

Onboarding Tasks

Task ID	Task Name
1000	Task 1
1010	Task 2
1020	Task 3

Branch Onboarding Tasks

Branch ID	Task ID
1234	1000
1234	1010
5678	1000
9999	1000
9999	1010
9999	1020

For branch 1234, the table should contain 1 row - Task 1020 - Task 3

For branch 5678 it should contain 2 rows - Task 1010 - Task 2 and Tasks 1020 - Task 3

For branch 9999 it should contain no rows

If I were writing SQL for this, I'd get all task IDs for the given branch from Branch Onboarding Tasks, then get all rows from Onboarding Tasks where the Task ID is not in the list from the first table.  I tried a merge into a new table (several versions of the join), but with no success.

Any ideas would be greatly appreciated!!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yangliu-msft · Accepted solution]

Hi  @lburgess ,

Here are the steps you can follow

1. Create measure.

Result = 
CALCULATE(SUM('Onboarding Tasks'[Task ID]),FILTER('Onboarding Tasks',NOT([Task ID]) in VALUES('Branch Onboarding Tasks'[Task ID])))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[lburgess]

Close but not quite.  My suspicion is the MAX in this measure is the issue.

Measure 2 = CALCULATE(max('Onboarding tasks'[Task Name]),FILTER(SUMMARIZE(Tasks,Tasks[Task ID],"ABCD",COALESCE(COUNTROWS('Branch onboarding tasks'),0)),[ABCD]=0))

The missing Task ID's may not be the max value.  My example may have thrown you off.  Here's a better example.

Sample data:

Onboarding Tasks

Task ID	Task Name
1000	Task 1
1010	Task 2
1020	Task 3

Branch Onboarding Tasks

Branch ID	Task ID
1234	1000
1234	1020
5678	1000
9999	1000
9999	1010
9999	1020

For branch 1234, the table should contain 1 row - Task 1010 - Task 2

For branch 5678 it should contain 2 rows - Task 1010 - Task 2 and Tasks 1020 - Task 3

For branch 9999 it should contain no rows

[Ashish_Mathur]

Hi,

You have not tried my solution.  I plugged in your revised data into my PBI file and my result matches yours.

 

[VijayP]

@lburgess 

I think that may not be possible in even in SQL because the join(merge) works based the similar columns in both tables and accordingly the data can be distributed to different rows.