Comparing if a value in one table can be found in another table through relationships

[Alex_Power_BI]

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!

[lbendlin · Accepted solution]

Surprisingly tough.  Requires visual level measure filters.

[Alex_Power_BI]

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 Timecard

    VAR 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 sheet

    VAR AssignmentExist =

    CALCULATE(  

        COUNTROWS('Assignments'),

        filter('Assignments', 'Assignments'[Employee] = 'Assignments'[selectedEmployeeAssignments]),

        filter('Assignments', 'Assignments'[Project Name] = 'Assignments'[selectedProjectAssignments])

    ) > 0

    Return MatchExist + AssignmentExist - 1

I 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.