Our first matrix visualization uses the Attendance measure. This measure displays Attended if the employee attended the training session in a week the employee worked and displays Not Attended if the employee did not attend the training session in a week the employee worked. To accomplish this, we start by getting the current training session, __Training, within the current context regardless of Employee. This is done by using CALCULATE coupled with ALLEXCEPT to override the filter context in order to remove all filters, including Employee from the R08_Employees table, but excluding the filter for the Training column in the R08_Training table.
We can now determine the date for the training, __TrainingDate. This is done by using ALL to strip away all filters from the R08_Training table, using FILTER to filter down to rows where the Training column equals __Training, and finally simply returning the maximum value for filtered rows using MAXX.
In our RETURN statement, we now first check to see whether the employee worked during the week in question, __TrainingDate. We do this by using FILTER to select only rows from the R08_Hours table where the Week column equals __TrainingDate, by counting the number of rows using COUNTROWS, and then by checking to ensure the value returned is greater than or equal to 1. If the value is not greater than or equal to 1, we simply return BLANK, as the employee did not work during that week. If the value is greater than or equal to 1, then the employee worked during that week. We then check to see whether the employee attended training by using MAX to return the Date column from the R08_Training table. If the value returned by this MAX statement is blank (ISBLANK), then the employee did not attend training and we return a value of Not Attended. Conversely, if the value returned by this MAX statement is not blank, then the employee attended training and we return a value of Attended.
When this Attendance measure is used within our matrix, we can see where employees attended and did not attend training, as well as blank values where employees did not work during the training week. However, you can imagine a grid of hundreds of training sessions and hundreds of employees where it would become quite cumbersome to visually review which employees did or did not attend training. What we would really like to do is use our Attendance measure in a slicer visualization to display Attended and Not Attended within the slicer, so that we could easily filter down to those employees that did not attend training. Unfortunately, we cannot use measures within slicers.
Enter the Disconnected Table Trick. As the name implies, to implement the Disconnected Table Trick, we need a disconnected table. In other words, we need a table that is not related to any other table in our model. This is the purpose of the R08_Attendance table. This table contains only two rows, Attended and Not Attended, which are the same as the values returned by our Attendance measure. We can now use the R08_Attendance table in our slicer. However, we need a method by which to relate the values in the R08_Attendance table back to our measure for attendance.
To relate the values in the R08_Attendance table back to our measure for attendance, we start by splitting our Attendance measure into multiple measures. While we only need two measures, one for attendance and one for non-attendance, here we use three measures, Worked, Attended, and Not Attended, because there is a significant portion of shared code.
Our Worked measure implements the first portion of our Attendance measure where we determine if an employee worked or did not work during the week of training. The code for the Worked measure is the same as the code for the Attendance measure except that we remove the portions of the code where we determine attendance and instead simply return TRUE or FALSE depending on whether the employee worked during the week of training or not.
We now break the portion of the Attendance measure that determines attendance into two separate measures, Attended and Not Attended. These measures first look at the return value from the Worked measure and, if the employee did not work during the training week, we simply return BLANK. If the employee did work during the training week, we use the same code as we did in the Attendance measure to determine whether the employee attended training. The Attended measure returns BLANK if the employee did not attend training and returns Attended if the employee did attend training. The Not Attended measure returns BLANK if the employee attended training and Not Attended if the employee did not attend training.
We now come to the measure that ties everything together, Attendance Measure to Show. The Attendance Measure to Show measure forms the bridge or relationship between our disconnected table, Attendance, and our Attended and Not Attended measures. We start by first checking to see if we have a single value for the Attendance column in the R08_Attendance table. We do this using the HASONEVALUE value function. If we do not have a single value for the Attendance column, we simply return the maximum value for the Date column in the R08_Training table. If we do have a single value for the Attendance column, we then implement a SWITCH statement based upon the value of the Attendance column. If the value in the Attendance column is Attended, we return the value from our Attended measure. If the value in the Attendance column is Not Attended, we return the value from our Not Attended measure. We have thus created the illusion of using our measure within a slicer.