Forum Discussion

Schwabinsky's avatar
Schwabinsky
New Member
1 year ago

Changing team assignment solution

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!

 

Need Help

4 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
      Schwabinsky
      New Member
      1 year ago

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

       

      Iputdata:

      dim_Employee:

      EmployeeIDEmployeeNameTeamStartDateEndDate
      1001Max MustermannTeam A01.01.202401.01.2100
      1001Max MustermannTeam B01.01.200031.12.2023
      1002Jane DowTeam C01.01.202401.01.2100
      1002Jane DowTeam D01.01.200031.12.2023

       

      dim_Date:

      DateDateKeyYearYearMonthQuarterDayOfMonthCalendarWeekDayOfYear
      30.12.20232023123020232023-1243052364
      31.12.20232023123120232023-1243152365
      01.01.20242024010120242024-011111
      02.01.20242024010220242024-011212
      03.01.20242024010320242024-011313
      04.01.20242024010420242024-011414
      05.01.20242024010520242024-011515

       

      fact_Projecthours:

      ProjectProjectprocessDateFK_EmployeeHours
      10189850001.01.202410014
      10189851002.01.202410014
      10189852003.01.202410024
      10189853004.01.202410024
      10190060001.01.202410016
      10190061002.01.202410016
      10190062003.01.202410026
      10190063004.01.202410026

       

      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)JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
      Team A16           
         Max Mustermann 16           
            10189816           
      Team C24           
         Jane Dow24           
            10190024           

       

      Thank you!

      Schwabinsky

       

  • mickey64's avatar
    mickey64
    Super User
    1 year ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.