Showing results for 
Search instead for 
Did you mean: 
New Member

Changing the Granularity Level | There are Some Caveats

Hi Everyone:
I am stuck with this conundrum. Could someone help, please?

The figure below is a snapshot of our Employee Lifecycle table, which contains information pertaining to every event in the employee lifecycle from Onboarding to Offboarding. Each production staff will be assigned to only ONE client at any point in time (primary client). A change in client can happen if the employee is laterally transferred, which will be recorded in the Employee Lifecycle table under the column titled "Transaction Type" as "Lateral Transfer."



As you can see above, the employee, Aaden Grimes started his job on 15-Mar-17 and left the company on 31-Jan-18. Throughout his employee lifecycle, he has worked only under Client_1.

The figure below shows a different scenario:



Olivia Zhang started her job on 12-Aug-15 under Client_1, and she stayed with the same client until 31-May-18. She was laterally transferred to Client_2 on 1-Jun-18 and back to Client_1 on the same day. She then stayed under Client_1 until 22-May-19 and got laterally transferred to Client_5 on 23-May-19.

I need to know, on any given day which "Primary Client" each employee was assigned to. The Employee Directory (employee master database) does contain information pertaining to every employee's primary client; however, this information is current, i.e., the Employee Directory might show Client_3 as an employee's primary client, but the same employee's primary client 2 years would have been Client_1. Therefore, the correct information regarding an employee's primary client at any point in time can be obtained only from the Employee Lifecycle table.
This means that I might have to first each employee's Start Date and End Date under each client and then generate all the dates between every client transition. Please see the screenshot below. (I did this manually, not using PQ).



To achieve my objective, I guess all dates between Eff_Start_Date and Eff_End_Date will have to be generated and the client names drilled down. However, there are some caveats:
  1. Grouping by Client cannot be done since an employee might be laterally transferred from Client_1 to Client_2 and then back to Client_1. Each of these transitions should be treated as separate stints. (Please see the column titled "Stint" in the above screenshot).
  2. If the employee has been offboarded (Transaction_Type = "Employee Offboarding"), the last Eff_End_Date should be the Effective_Date of Employee Offboarding.
  3. If the employee has not been offboarded, the last Eff_End_Date should be dynamically updated with today's date.

The current granularity level of the Employee Lifecycle table is a lifecycle event, which needs to be changed to the level of each day in the lifecycle of every employee.


I hope someone would be able to help out. Many thanks in advance.



New Member

Hi @Eyelyn9 


Many thanks for your reply.

You can access the sample file here:

On the 2nd tab of the file, columns F, G, and H have been manually done by me. I need to create those columns using PQ, then fill down all the dates between "Eff_Start_Date" and "Eff_End_Date."

I have used fake employee and client names to protect confidentiality.

Many thanks for responding.  Hope you'll be able to help.



Community Support
Community Support

Hi @ajieapen ,


So according to my understanding, the new table you want to create contains every row with a change in the Transaction Type of each employee.


Could you please share some dummy data in table format not picturesfor convenience? Or share me with your pbix file after removing sensitive data.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community



Best Regards,
Eyelyn Qin

Helpful resources

November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors