cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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.

Anonymous

@peytonmcbrayer I have got exactly the same problem as you do. Did you manage to find a solution for it?

Thank you so much in advance for any hint!

@Greg_Deckler Have you maybe encountered a limitation like this one? Your help is greatly appreciated!

Anonymous

Thanks for your quick help and solution but I am not able to do the same in my chart.

Can you please elaborate more using my data ? It will be great help from your side.

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Hi @Greg_Deckler
I need your expertise. I am looking for a similar kind of solution where I have a "Name" column and "Six months" as column names. Each month has values, and I need to create a filter where I can select a particular month, for example, February. In the table, it should show all columns like Name, Jan, Feb, Mar, Apr, May, and June. However, for the unselected months, the values should be displayed as zero or blank.