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...
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.
