Forum Discussion
Solved
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 bring...
Surprisingly tough. Requires visual level measure filters.
lbendlin
Super User
Super UserSurprisingly 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 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.