Forum Discussion

Sisyfos-XY's avatar
Sisyfos-XY
New Member
5 years ago

Using relationships in temporary table that calculates workers per hour based on actual hours table.

I have data that has StartHour and EndHour for each employee in my organization for every time they have come to work and left. From that I want to create a hourly manning schedule. To do that I followed the instructions here:

https://community.powerbi.com/t5/Desktop/Counting-numbers-per-hour-between-a-start-and-end-time/td-p/591494

 

And I have a temporary table and a formula that counts the workers per hour data:

WorkerPerHour = CALCULATE(COUNTROWS('Actual_Hours'), FILTER(ALL('Actual_Hours'), 'Actual_Hours'[StartHour] <= Worker_per_hour_temp[Value] && 'Actual_Hours'[EndHour] >= Worker_per_hour_temp[Value]))
 
That "Value" in here is just a list of hours (integers) from 0 to 30 (I have moved the hours past midnight to continue the hours so that comparison above works, but that is a separate problem). The StartHour and EndHour are just integer values. 
 
This all seems to create correct values. My problem is this though: how can I make this data follow my filters? It has no relation to the original datatable it seems and does not follow date filters, location filters etc. It just pulls all the hours for every day in the data and summarizes those. Do I need to add columns but how? I tried to bring in the location column, but that did not seem to work. It would also feel weird to bring here all different possible slicers instead of connecting this to the data model itself properly so perhaps I need to do something with the "Manage relationships", but when I tried that it did not seem to work either. I am propably missing something really simple here in the original formula that was used to do the calculaterows pars, but as I am rather new with Power BI, I am not able to figure it out it seems.
 
Any help would be appreciated.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Sisyfos-XY ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Sisyfos-XY's avatar
      Sisyfos-XY
      New Member
      5 years ago

      Hi, 

      Thanks! I can prepare those examples, but it will have to be next week. Burning midnight oil now preparing for a deadline and I have to be pretty careful to make sure everything is properly anonymized as the data contains personal information and as I said I am not that familiar with Power BI and might miss something.