Changing team assignment solution

[Schwabinsky]

Hello everyone,

I have the following problem when customizing my report. We already have a report that tracks the workload of our project teams. Simplified, the report consists of a project table (project hours per employee, per project, per day), a date table (dim_Date) and an employee table (dim_Employee). The report currently functions on the basis of the current data and the current team assignment.

Now we would like to customize the report so that team changes in the past are also taken into account and by selecting a date, a team assignment as it was on a certain date in the past can be seen.

The solution approach was as follows: The plan was to adjust the current employee table (employee A, department A) so that department changes are now also included (employee A, department A, start date, end date; employee A, department B, start date, end date). This causes the following problems: The IDs of the employees now exist several times in the employee table, which is why the data model can no longer be set up.

Does anyone have a similar problem with a report that wants to dynamically incorporate dimensions based on dates in the past into a report? Is there a best practice for this?

Thank you!

[Anonymous]

Hi @Schwabinsky ,

You can create a date slicer, then create a measure [Flag] and place [Flag=1] on the visual object filter.

date slicer = CALENDAR(DATE(2000,1,1),DATE(2100,12,31)) 

Flag = 
 var _min_slicer=MINX(ALLSELECTED('date slicer'),[Date])
 var _max_slicer=MAXX(ALLSELECTED('date slicer'),[Date])
 RETURN IF(MAX(dim_Employee[StartDate])=_min_slicer && MAX('dim_Employee'[EndDate]) =_max_slicer,1,0)

According to your data, dim_Employee and fact_Projecthours are many-to-many relationships, TeamA corresponds to 1001 and then 1001 corresponds to 101898 and 101900 respectively, can you explain why 1001 only corresponds to 101898 in the output? If I missed something please add it in a follow up reply.

Instead of directly associating many-to-many tables, we usually recommend that you consider a star schema.

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Many-to-many relationship guidance - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[mickey64]

For your reference.

Step 0: I use these data.

Step 1: I add a 'Current Team' column to the 'Project' table.

Current Team =

    CALCULATE(

        MAXX(

            FILTER(ALL('Employee'),

                'Employee'[Start Date]<=MIN('Project'[Date])

                &&'Employee'[End Date]>=MIN('Project'[Date])

                &&'Employee'[Employee]=MAX('Project'[Employee])

            ),

            'Employee'[Department]),

        ALLEXCEPT('Employee','Employee'[Employee])

     )

Step 2: I make some 'Matrix's.

[danextian]

Hi @Schwabinsky 

I have a general idea of what to do but without a sample data it's hard to test a solution. Please provide a workable sample data (not an image), your expected result from that and the reasoning behind.

[Schwabinsky]

Hi denextian, thank you for your fast response. In the following, ich will show you the example data.

Iputdata:

dim_Employee:

EmployeeID	EmployeeName	Team	StartDate	EndDate
1001	Max Mustermann	Team A	01.01.2024	01.01.2100
1001	Max Mustermann	Team B	01.01.2000	31.12.2023
1002	Jane Dow	Team C	01.01.2024	01.01.2100
1002	Jane Dow	Team D	01.01.2000	31.12.2023

dim_Date:

Date	DateKey	Year	YearMonth	Quarter	DayOfMonth	CalendarWeek	DayOfYear
30.12.2023	20231230	2023	2023-12	4	30	52	364
31.12.2023	20231231	2023	2023-12	4	31	52	365
01.01.2024	20240101	2024	2024-01	1	1	1	1
02.01.2024	20240102	2024	2024-01	1	2	1	2
03.01.2024	20240103	2024	2024-01	1	3	1	3
04.01.2024	20240104	2024	2024-01	1	4	1	4
05.01.2024	20240105	2024	2024-01	1	5	1	5

fact_Projecthours:

Project	Projectprocess	Date	FK_Employee	Hours
101898	500	01.01.2024	1001	4
101898	510	02.01.2024	1001	4
101898	520	03.01.2024	1002	4
101898	530	04.01.2024	1002	4
101900	600	01.01.2024	1001	6
101900	610	02.01.2024	1001	6
101900	620	03.01.2024	1002	6
101900	630	04.01.2024	1002	6

The following visual shows the data as it should look if a date between “01.01.2024” and “01.01.2100” is selected via a date slicer. If you now change the date to a date in the past in the period from “01.01.2000” to “21.12.2023”, then the team of Max Mustermann should change to “Team B” and the team of “Jane Dow” should change to “Team D”:

Drillthrough (Team > Employee > Project)

January

February

March

April

May

June

July

August

September

October

November

December

Team A

16

Max Mustermann

16

101898

16

Team C

24

Jane Dow

24

101900

24

Thank you!

Schwabinsky