cancel
Showing results for 
Search instead for 
Did you mean: 

Solving Attendance with the Disconnected Table Trick

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

EmployeeTrainingDate
GregTraining 19/19/2017
BillTraining 19/19/2017
JoeTraining 19/19/2017
DavidTraining 19/19/2017
JimboTraining 19/19/2017
GregTraining 28/19/2017
BillTraining 28/19/2017
DavidTraining 28/19/2017
GregTraining 37/19/2017
BillTraining 37/19/2017
JoeTraining 37/19/2017
DavidTraining 37/19/2017

 

Hours

EmployeeWeekHours
Greg9/17/201740
Greg9/10/201740
Greg9/3/201740
Greg8/27/201740
Greg8/20/201740
Greg8/13/201740
Greg8/8/201740
Greg8/1/201740
Joe9/17/201724
Joe9/10/201724
Joe9/3/201724
Joe8/27/201724
Joe8/20/201724
Joe8/13/201724
Joe8/8/201724
Joe8/1/201724

 

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:

 

training1.png

 

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:

 

training2.png

 

 

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:

 

training3.png

 

 

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.

Comments

Added tracking counter:

This is a great post!  It's wicked close to what I'm trying to do, but I can't seem to get it to work for my scenario.   I have an Attendance table that includes incidents for students where they missed a day, and a Students table that has a unique record for each Student.   I currently have measures that calculate the total absent days, and the total school days for each month, and a measure that calculates the Absent %.  I even took it a step further and created a measure that builds the absence rate category.  I have tried everything to get it working so I can show the rate category in the legend of a stacked bar chart, but nothing works.  It seems like the context of the column is not evaluating correctly.

 

:: This measure calculates the total incidents 

ABM Total Absenses = SUM('Attendance by Month'[Count Absent])

:: This is a related table to the students that has the # of operational days for each month

ABM Total Operating Days = sum('School Operational Days by Month'[Operating Days])

:: This measure calculates the Absense %

ABM Absence % = DIVIDE([ABM Total Absenses], [ABM Total Operating Days])

:: This measure evaluates the absense %, and spits out the resulting category.

ABM Absense Category =
IF([ABM Absence %] >= 0 && [ABM Absence %] < .0999, "< 10%",
IF([ABM Absence %] >= .10 && [ABM Absence %] < .1999, "10% to 19.9%",
IF([ABM Absence %] >= .2 && [ABM Absence %] < .2999, "20% to 29.9%",
IF([ABM Absence %] >= .3 && [ABM Absence %] < .3999, "30% to 39.9%",
IF([ABM Absence %] >= .4 && [ABM Absence %] < .4999, "40% to 49.9%",
IF([ABM Absence %] >= .5, "> 50%",
"OTHER"))))))

 

What I'm missing is to create a calculated column from ABM Absense Category so I can use it as a legent in my visual, with a count of students that fall in each category.   Can you help?  I'm totally stumped...  The calculations and everything look right when I load a table visual for QA, except for the COLUMN.