Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi you all,
I have the following issue on which I could use some assistance. I would like to create a Matrix showing the number of hours an employee has worked based on the relavent employment at that time.
My fact "hours table" looks like
Name | Hours | date |
Jane | 3 | 1-2-2020 |
Jane | 5 | 4-4-2020 |
I have a dim table "employee contracts"
Name | Contract begin | Contract end | Department | team |
Jane | 1-1-2020 | 28-2-2020 | HR | HR1 |
Jane | 1-3-2020 | Payrol | Payrol2 |
I Also have a separte date table.
My output should be a matrix on a report page also containing a week and month slicer. If I would select the whole year the matrix should look like this
Department | Team | Name | Hours |
HR | HR1 | Jane | 3 |
Payroll | Payrol2 | Jane | 5 |
If i would select april the matrix should look like:
Department | team | Name | Hours |
Payroll | Payroll2 | jane | 5 |
I would like to add that my "hours" table contains millions of rows so I do not want to add colomns to this fact table due to performance issues. So i am looking for the most effective way of solving this issue.
Solved! Go to Solution.
Hi @BobKoenen ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @BobKoenen ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@BobKoenen , You can have new columns like these in hours table
Department = maxx(filter(contracts,contracts[Strat Date] <=hours[date] && (isblank(contracts[end Date]) || isblank(contracts[end Date]) >=hours[date])),contracts[Department])
team = maxx(filter(contracts,contracts[Strat Date] <=hours[date] && (isblank(contracts[end Date]) || isblank(contracts[end Date]) >=hours[date])),contracts[team])
Thankx Amitchandak. But is going to add a column in a fact table not going to ruin my performance. It is a fact table of milions of rows on which i am going to ad another column
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |