Create a matrix with hours of employees based on active employement

[BobKoenen]

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. 

[v-lionel-msft · Accepted 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.

[amitchandak]

@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])

[BobKoenen]

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