Forum Discussion
Helper IISelect rows from table 2 that are not in table 1
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!!
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
5 Replies
- Ashish_Mathur
Super User
lburgessClose 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.
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
