Forum Discussion
Comparing if a value in one table can be found in another table through relationships
Hello,
I'm struggling to create a DAX measure solution for the following scenario:
We have employees that record hours in a virtual timecard system. This is a corporate dataset that I'm bringing into my Power BI data model. The managers in our department keep their own Excel sheets to assign their direct reports projects and to keep track of their assignments. I import their excel sheets into the data model and relate them to the dataset via Direct Query. I would like to create a measure that compares the projects for an employee on the manager assignement sheet to the projects employees record time against in the virtual time card system (and vice versa). I have example tables below simplifying the data.
The data tables in the data set include:
Supervisor_Table
| Employee | Supervisor | Employee ID |
| Alex | Sarah | 101 |
| Brian | Sarah | 102 |
| Chris | Sam | 103 |
| Dan | Sarah | 104 |
| Frank | Sam | 105 |
Resources
| Employee ID | Cost Center | Resource Code |
| 101 | A1 | 2001 |
| 102 | A1 | 2002 |
| 103 | A2 | 2003 |
| 104 | A1 | 2004 |
| 105 | A2 | 2005 |
Hours
| Resource Code | Hours | Activity Code |
| 2001 | 20 | W |
| 2001 | 15 | X |
| 2001 | 5 | Y |
| 2002 | 18 | Z |
| 2002 | 22 | W |
| 2003 | 40 | X |
| 2004 | 9 | Y |
| 2004 | 31 | Z |
| 2005 | 35 | Y |
Activity
| Activity Code | Project Code | Project Name |
| W | 31 | Project A |
| X | 31 | Project A |
| Y | 32 | Project B |
| Z | 33 | Project C |
The manager assignment tracker table looks like:
Assignments
| Employee | EID | Project Name |
| Alex | 101 | Project A |
| Alex | 101 | Project B |
| Alex | 101 | Project C |
| Brian | 102 | Project A |
| Brian | 102 | Project C |
| Chris | 103 | Project A |
| Chris | 103 | Project C |
| Dan | 104 | Project C |
| Dan | 104 | Project A |
| Frank | 105 | Project B |
| Frank | 105 | Project A |
The data model I built from these tables is depicted below:
The resulting tables I'd like to get are:
Result 1 (Comparing Manager Assignments to Timecard)
| Employee | EID | Project Name | Matches Timecard? |
| Alex | 101 | Project A | TRUE |
| Alex | 101 | Project B | TRUE |
| Alex | 101 | Project C | FALSE |
| Brian | 102 | Project A | TRUE |
| Brian | 102 | Project B | FALSE |
| Brian | 102 | Project C | TRUE |
| Chris | 103 | Project A | TRUE |
| Dan | 104 | Project C | TRUE |
| Dan | 104 | Project A | FALSE |
| Frank | 105 | Project B | TRUE |
| Frank | 105 | Project A | FALSE |
Result 2 (Comparing Timecard projects to Manager Assignments)
| Employee | Resource Code | Hours | Activity Code | Project Name | Matches Manager Assignment |
| Alex | 2001 | 20 | W | Project A | TRUE |
| Alex | 2001 | 15 | X | Project A | TRUE |
| Alex | 2001 | 5 | Y | Project B | TRUE |
| Brian | 2002 | 18 | Z | Project C | TRUE |
| Brian | 2002 | 22 | W | Project A | TRUE |
| Chris | 2003 | 40 | X | Project A | TRUE |
| Dan | 2004 | 9 | Y | Project B | FALSE |
| Dan | 2004 | 31 | Z | Project C | TRUE |
| Frank | 2005 | 35 | Y | Project B | TRUE |
Any help would be greatly appreciated. Thank you in advance!
Surprisingly tough. Requires visual level measure filters.
3 Replies
- lbendlin
Super User
Surprisingly tough. Requires visual level measure filters.
Thank you for the help! This is great! I did work on it a little more after I posted this and came up with the following:
MatchesTimecard =VAR selectedProjectAssignments = SELECTEDVALUE('Assignments'[Project Name])VAR selectedEmployeeAssignments = SELECTEDVALUE(Assignments[Employee])//Variable for counting if line item exists in TimecardVAR MatchExist =COUNTX(CALCULATETABLE('Hours',filter('Supervisor_Table', 'Supervisor_Table'[Employee] = 'Assignments'[selectedEmployeeAssignments]),filter('Activity', 'Activity'[Project Name] = 'Assignments'[selectedProjectAssignments])), 'Hours'[Hours]) > 0//Variable for counting if line item exists in the original Assignments sheetVAR AssignmentExist =CALCULATE(COUNTROWS('Assignments'),filter('Assignments', 'Assignments'[Employee] = 'Assignments'[selectedEmployeeAssignments]),filter('Assignments', 'Assignments'[Project Name] = 'Assignments'[selectedProjectAssignments])) > 0Return MatchExist + AssignmentExist - 1I couldn't quite get the T/F and was uing another measure to filter the table.Just curious, the measure filter is needed because of the Cartesian Product of the data tables right?- lbendlin
Yes, without it too many combinations would show.
