Solving Attendance with the Disconnected Table Trick

[Greg_Deckler]

The Challenge

At a recent Columbus Azure ML and Power BI User Group (CAMLPUG) meeting, a member came forward with the following problem around tracking training attendance. Essentially, given a set of data that tracked employee attendance of training sessions, how to report on those employees that did not attend the required training? The scenario presented below shows a simplified form of the data involved as a means of demonstrating the solution to this problem.

The Data Model

Given the set of these core tables:

Employees

Employee

Greg

Bill

Joe

David

Jimbo

Training

Employee	Training	Date
Greg	Training 1	9/19/2017
Bill	Training 1	9/19/2017
Joe	Training 1	9/19/2017
David	Training 1	9/19/2017
Jimbo	Training 1	9/19/2017
Greg	Training 2	8/19/2017
Bill	Training 2	8/19/2017
David	Training 2	8/19/2017
Greg	Training 3	7/19/2017
Bill	Training 3	7/19/2017
Joe	Training 3	7/19/2017
David	Training 3	7/19/2017

Hours

Employee	Week	Hours
Greg	9/17/2017	40
Greg	9/10/2017	40
Greg	9/3/2017	40
Greg	8/27/2017	40
Greg	8/20/2017	40
Greg	8/13/2017	40
Greg	8/8/2017	40
Greg	8/1/2017	40
Joe	9/17/2017	24
Joe	9/10/2017	24
Joe	9/3/2017	24
Joe	8/27/2017	24
Joe	8/20/2017	24
Joe	8/13/2017	24
Joe	8/8/2017	24
Joe	8/1/2017	24

The Employees table is simply a unique list of Employee ID's. The Training table has a row for every training event that an employee attended. Finally, the Hours table lists the week ending date when an employee worked. Employees are expected to attend training events when they are working and are not expected to attend training events when they are not working. The Hours table has been abbreviated.

The relationships between the tables are as follows. All relationships are between the Employee columns of the tables.

Employees 1<>* Hours

Employees 1>* Training

A Simple Solution

Without doing any work, we can begin by creating a slicer based upon the Training[Training] column and a matrix with Employees[Employee] as the Row, Training[Training] as the Column and Training[Date] (Earliest) as the Value. This provides a sliceable matrix as follows:

This is useful to visually see the holes in the training attendance. However, if an Employee has not attended any training sessions for the selected training, then they will not show up in the matrix because the matrix filters out all blank rows. In addition, when exporting the data, the missing attendance items are not exported.

A Slightly More Advanced Solution

To solve this, we can create a measure as follows:

Attendance = IF(ISBLANK(MAX([Date])),"Not Attended","Attended")

We can then modify our matrix to use this Attendance measure as the Value and achieve the following:

This solves the issue of blank rows not appearing and the non-attendance being able to be exported. However, it would be preferred if the list could be filtered down to just the non-attendance. Unfortunately, because Attendance is a measure, it cannot be used in a slicer. To solve this, we can introduce the Disconnected Table Trick.

An Advanced Solution

The Disconnected Table Trick essentially forms an intrinsic relationship with a disconnected table through the use of a measure. The disconnected table contains a single column with one row for every value that we wish to show, in this case "Attended" or "Not Attended". In addition, we must create a measure for each value that we wish to show, again, in this case "Attended" and "Not Attended".

We start by creating the following two measures based upon the logic from our previous "Attendance" measure:

Attended = IF(ISBLANK(MAX([Date])),BLANK(),"Attended")
NotAttended = IF(ISBLANK(MAX([Date])),"Not Attended",BLANK())

We then create our disconnected table using an Enter Data query:

Attendance

Attendance

Attended

Not Attended

Finally, we create our measure that ties everything together:

Measures to Show = 
IF(
        // This first IF condition forces the measure to evaluate in the context of the visual
	HASONEVALUE(Employees[Employee]),
                // This next condition avoids getting the error that the visual cannot be displayed
		IF(HASONEVALUE(Attendance[Attendance]),
                        // This switch statement uses the values from our Attendance slicer to determine which measure to display
			SWITCH(
          			VALUES(Attendance[Attendance]),
          			"Attended",[Attended],
          			"Not Attended",[NotAttended]
	 			),
                        // If the Attendance slicer has not been selected, just display the date of the training
			MAX([Date])
		)
)

We can now put this all together by placing our "Measures to Show" measure into the Values area of our matrix. We can also add an Attendance slicer based upon our disconnected "Attendance[Attendance]" column and a date range slicer based off of our Hours[Week] column as show below:

We now have a solution where it is easy to pinpoint which Employees did not attend training for a specific training and only for the employees that were working during a specified range of time.

Conclusion

The Disconnected Table Trick can be used to solve complex issues where a measure is used to create a categorization and the user needs to be able to utilize that categorization within situations where a measure cannot be used, such as slicers as well as the axis of visualizations such as bar charts.

EmployeeTraining.pbix