Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started


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:






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





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:



Not Attended


Finally, we create our measure that ties everything together:


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

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.



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.


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%",


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.




@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!


Hi @Greg_Deckler , 

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.


Thanks in advance.


@Anonymous What is your data? Please see this post regarding How to Get Your Question Answered Quickly:

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.

Thank you in advance.

@harirao Not quite sure I am following, can you post sample data and expected output?

Hi @Greg_Deckler,


This is really helpful. I am however having difficulties figuring out another scenario to put in a donut chart.


I have a table of countries with corresponding market values. If an entry is <10% of the sum total of market value, it needs to go to "Others", otherwise it should still be mapping to that country.  Sample table and expected output below. 


CountryMarket Value Percentage %Expected Mapping


I am able to create a measure but it doesn't let me put in a legend hence would need to convert it to a column. I tried doing a calculated table but it doesn't recalculate when I slice and dice it via the file date.


Can you please advice?



@joanne1 So, I think you need a, disconnected table with a single column with all of your country names as well as Others. Could do this as a via an Enter Data query or a DAX formula. That goes in your legend. Then your measure would have to calculate the percentage involved and determine what to display, such as:

Measure =
  VAR __Country = MAX( 'Legend Table'[Country] ) //disconnected table
  VAR __Table = 
      "__Percent", DIVIDE( [Market Value], SUM( [Market Value], 0 )
  VAR __Others = SUMX( FILTER( __Table, [__Percent] < .1 ), [Market Value] )
  VAR __MarketValue = MAXX( FILTER( __Table, [Country] = __Country ), [Market Value] )
  VAR __Percent = MAXX( FILTER( __Table, [Country] = __Country ), [__Percent] )
  VAR __Result =
      __Country = "Others", __Others,
      __Percent < .1, BLANK(),