In this tutorial, you’ll learn how to work out the current staff population for multiple dates in Power BI using the COUNTROWS and FILTER function. You may watch the full video of this tutorial at the bottom of this blog.
This discussion is based on a question in the Enterprise DNA support forum.
A Power BI user wanted to calculate the number of staff members that have been hired within specific time periods.
For this problem, it’s important to understand how the data is set up. You should have the employee names and the dates when they started and left, among other details. These are common HR-related data.
To make this technique work, you must use inactive relationships and a type of formula that enables you to count the numbers between dates of the same row.
Staff Population Data And Model
Let’s look at the demo data.
You can see that the table uses staff reference numbers. They are useful because HR databases usually contain a lot of information.
There’s also the Start Date and End Date. The End Date returns blank if the person is still an employee of the company.
Now, to find out the number of employees for a certain time period, you first need to set up inactive relationships in your model.
When you have multiple dates in your fact table, you need to have inactive relationships so that you can work out the information or logic between specified dates.
For instance, if you want to calculate the number of staff that started within a specific month, you’d need an active relationship between the Date table and Staff Population table.
Using inactive relationships allows you to choose which elements in your facts and lookup table to turn on to make your calculation work.
Choosing The Context
For this example, the dates have a month and year context.
You can make the table show a day context by simply changing the Values under the Visualizations pane.
This table can also be turned into a visualization:
The chart with a day context has a more granular level of detail compared to the one with a month and year context.
Formula For Current Staff
Each result for the Current Staff row should be calculated individually.
You need to isolate each value and work through the logic behind it.
To understand this concept better, the row with May 2018 will be used as an example:
Now, to calculate the number of current staff, you need to use the events in a progress formula pattern:
The COUNTROWS Function
The COUNTROWS function is counting the number of staff members within each row in the Staff Population table.
VALUES Function And The FILTER Statement
Let’s now focus on the VALUES functions and FILTER statements inside the formula.
For the first logic, the VALUES inside the FILTER function iterate through every single Start Date in the Staff Population table:
If the Start Date is less than or equal to the MAX date, it will evaluate to TRUE.
For the May 2018 row, the MAX date is May 31. The first logic gives you a list of all the staff members who have start dates less than the last day of May 2018.
Let’s now look at the second logic in the formula:
It iterates through the end dates of each row in the Staff Population table containing start dates that have been retained after the first logic was run.
If the End Date is greater than or equal to the MIN Date, it will evaluate to TRUE.
For this example, if the end date is greater than the first day of May, the staff or employee still exists within that specified time frame.
The ISBLANK Function
The ISBLANK function evaluates if the End Date is blank.
If the End Date returns blank, it means that the staff is still employed.
Thus, there are two conditions for the second logic in the formula. First, it needs to evaluate if the End Date is greater than or equal to the first day of the month; and second, if it’s BLANK.
After the formula iterates through those two logics, it then counts the remaining data in the Staff Population table.
The FILTER inside the CALCULATE function changes the context of the calculation.
***** Related Links *****
How To Work With Multiple Dates In Power BI
Master Virtual Tables In Power BI Using DAX
Counting Customers Over Time Using DISTINCTCOUNT In Power BI
Conclusion
This tutorial covered how you can count the number of current staff within multiple dates using the COUNTROWS and FILTER function in Power BI.
To summarize, the FILTER statements build a virtual table containing data that fits the criteria being evaluated. Then, the COUNTROWS function counts the remaining data which gives the number of current employees within specific time periods.
Inactive relationships are needed to make this technique work. They make sure that you’re iterating through every staff member first, and then working through the filtered data. This method guarantees the correct staff count per date. An active relationship would’ve given the incorrect calculation.
Multiple dates scenarios can be very confusing when you’re staring out in Power BI. With this tutorial, you’ll have a better understanding of how to use this technique in your own models.
All the best,
Sam
