In this writing, I want to share how I try to create a caluclated table that shows who are active employees on each date in Power BI.
Until today, when I see an Employee table structured like below, I usually create a DAX measure in order to count active employees in a selected period. I will try to share how I usually do in a different blog post later.
But now, I want to try to create a calculated table that shows which employees are active on each date. I think this will make it easy to create an active-one-to-many relationship with a calendar table.
As shown in the above, there are two date-columns in the Employee table. We can start to think that one column can have active relationship with the calendar table, and other can have inactive relationship with the same calendar table. This may be a good start.
However, if I can create a calculated table that has one date column and one employee column that shows active employees, I can simply have a one-to-many relationship that is always actively related with a calendar table. This simplified data model structure will help to make most of analysis easier to understand and write simpler DAX measures.
In my imagination, I think I need to try to create something like a below table.
Let's go to the DAX Studio and let's try.
I try to explain my thought process how I wrote the DAX formula.
- Define startdate by seeing the minimum date of all employees' Start date column, and define enddate by seeing the maximum date of all employee's End date column.
- Create a virtual calendar table that contains one column (date-column), that starts at the startdate that is defined above, and ends at the enddate that is defined above.
- Use GENERATE DAX function to combine a calendar table (first table) and the employee table (second table). GENERATE DAX function returns a table that results from evaluating the second table in the context of the current orw from the first table.
- Summarize this table only to show Date and Active employees.
It is perfect. So, I created a calculated table like below. Additionally, I create a calendar table and a relationship like below.
One thing to note is that I did not create a calendar table first and then used this inside the DAX formula to create a New Employee table. One of reasons is because in some cases, there might be a situation that shows circular dependency when creating a relationship.
To summarize, one of ways to achieve calculating how many active employees in the selected period is to create a calculated table and make it to have one active-one-to-many-relationship with a calendar table. This makes easy to understand, analyze, and make a simple data model.
I hope this blog post helps you have fun playing with GENERATE DAX function.
Comments