This month's data story is courtesy of Sarah (sk15227) from the Power BI Community forum. Power BI makes it really easy to show relationships within our data. We can easily filter for a region and date range to see which employees were in attendance or which products were sold, or how many people filled in the daily paperwork.
Sarah wanted to know which employees did NOT complete the paperwork each day. More than a count, she wanted to know their names - who does she need to chase up?
Show Records NOT IN results / filter / selection
Sarah's question is a common request I get with Power BI. How can I filter for a specific region/category/segment and show the missing records? This applies to more than just Sarah's employee form example, but her scenario is so simple and yet powerful that I thought it was the perfect story to highlight for my data story of the month.
The Challenge: Find missing records
We will start with three simple tables in our data model:
- Employees
- FormSubmissions
- Calendar
The Employees table has three columns: ID, Name and Station.
The FormSubmissions table has two columns: EmployeeID, Submission Date.
The Calendar table is a typical DimDate table and should have any columns you want. As a good practice starting point you will need at least a Date and DateKey columns.
Without doing any DAX calculations, I can create a simple report to show the:
- number of submissions per Employee (Count of Submission Date)
- number of employee forms submitted per Date (Count of Employee ID)
As you can see in the image above, the North Station has 5 total employees. In the top chart, you can see that we are missing some forms from Alison, Dave and Eric. Alison has only submitted 21 forms while Bob and Charlie each submitted 24. Dave and Eric also appear to be missing a few submissions, but Alison is the worst offender.
How can we determine which dates these employees are missing? The Submission Date and Count of Employee ID shows that Feb 1, 2, 9, ... have not received submissions from all 5 employees. I could even filter this second table for Count of Employee ID less than 5, but this won't update when I select another station (the South station only has 3 employees).
Even if the filter were dynamic, it still requires me to know my employees by heart as it only shows the employees who have filled in the form. We want to see the employees who have not filled in the form. Below you can see that Eric is not in the list when I select Feb 1, but I want to see Eric's name listed as 'MissingForm'.
The Solution: DAX 'NOT IN'
To solve this challenge we will use the DAX function NOT() in combination with the IN operator. First, we will create a list of all the Employees who have completed the form within the current slicer and filter selections using:
VAR formcomplete =
VALUES ( FormSubmissions[Employee ID] )
Then, we will check that list of Employees who have completed the form against the entire list of ALLSELECTED Employees, again within the current slicer and filter selections, using:
FILTER (
ALLSELECTED ( Employees ),
NOT ( Employees[ID]
IN formcomplete )
)
Finally, we will use the CONCATENATEX function to create a simple list of all Employee Names separated by commas. The final DAX function becomes:
MissingForm =VAR formcomplete =
VALUES ( FormSubmissions[Employee ID] )
RETURN
CONCATENATEX (
FILTER (
ALLSELECTED ( Employees ),
NOT ( Employees[ID]
IN formcomplete )
),
Employees[Name],
", "
)
And as you can see, this gives us the names of the employees who have not completed the form on any given day and highlights that no one completes the form on Sundays.
