Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Daily Mapping table

Hi Everyone,   I'm currently in a challenge with creating a daily mapping table , the scenario is :   I'm creating a employee daily snap shot for HR in Power BI, I have 1 fact table(exported dail...
Data Protection
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous 

Can you provide more details on needing to "add new lines everyday to keep the relationship btw those two tables".

If i understood your post correctly, i don't see the need for creating new lines everyday. If there is a need by HR do have a daily, monthly quarterly it is possilble. But you would need to change your mapping key to teams as using date is not advisable as the dates are infinite values .

BR

Dax_Noob

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

 

Thanks Anonymous , I have highlighted the lines  I added for 3 Aug and I will need to do this everyday otherwise the relation will broken(via Helper column)

 

If use Teams as mapping key instead of  creating a Helper column, it won't keep the track record of the mapping correctly, especially for historical date.  For example if I simply change Team1 from Dep-A to Dep-B in the mapping table , then all the employees under Team 1 will be mapped to Dep-B from the time I made this change.  If we filter the dashboard to show headcounts as at 1 Aug, anyone under Team1 will be mapped to Dep-B which is not correct since Team 1 should be under Dep-A as at 1 Aug.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    JCan you provide a screenshot of your report which some blurred out data. As that would help to understand your use case.

    BR

    Dax_Noob


    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Anonymous , here is a quick mock up chart, basically we just select a date and it will show the count of employees for each Deparment as at selected date

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

        Imo, would suggest to keep the dimension table small without duplicates. Since each employee is unique you can create a employeeid in the dimension table as a unique key but that would require you to modify your dimension and fact table. 

        Step 1: Modify your dimension table to the below,  A new row is created whenever structural change happens (Note: if you do not want to use employee id you could use employee name as the mapping, but not advisable as some employees have same first name)

         

        Step 2: Map Employee Id to Employee id, Or employee name to employee name


        Step 3: Visualize



        I hope that helps, if it does please mark that as a solution

        BR

        Dax_Noob