cancel
Showing results 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

 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.

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.

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.

Top Kudoed Posts
Latest Articles
Archives
Polls
What is your favorite Power BI feature release for September 2023?