Forum Discussion
Use filter but avoid propoagation effect
Hola,
I'm trying to calculate how much an employee is spending time on actual projects:
'productivity' = [total hours worked on projects] / [total hours that the employee was available]
I'm given a schema like:
WorkDone = (employee_id, date, project_id, hours)
HoursAvailable = (employee_id, date, available_hours)
So to calculate productivity, I have a measure in WorkDone table:
productivity = Sum(WorkDone.hours)/Sum(HoursAvailable.hours)
In powerBi desktop, I have a page where I do a range of analysis on WorkDone, and it includes a date filter (Timeline control).
The problem: after a date range is selected, rows on WorkDone are filtered and if there is no work done by the employee in those dates, nothing is selected (0 hours worked), but the filter also propagates to HoursAvailable and I get also 0 for hours available.
I should be using ALL with FILTER in the denominator, but how to access the dates chosen in Timeline control?
I created a new AllDates(id, date) table with 1:* relationship with Tornado and 'public employee_schedule' on date column.
In my page, I use AllDates.date for filtering, which propagates to both Tornado and public 'public employee_schedule'.
Finally I use Format > Edit Interactions to avoid any other unwanted filters/slicers affecting my measure (that is shown in a KPI card).
Thanks guys for the help.
10 Replies
Hallo,
I'm trying to calculate how much an employee is spending time on actual projects:
'productivity' = [total hours worked on projects] / [total hours that the employee was available]
I've given a schema like:
WorkDone = (employee_id, date, project_id, hours) HoursAvailable = (employee_id, date, available_hours)
So to calculate productivity, I have a measure in WorkDone table:
productivity = Sum(WorkDone.hours)/Sum(HoursAvailable.hours)
In powerBi desktop, I have a page where I do a range of analysis on WorkDone, and it includes a date filter (Timeline control).
The problem: after a date range is selected, rows on WorkDone are filtered and if there is no work done by the employee in those dates, nothing is selected (0 hours worked), but the filter also propagates to HoursAvailable and I get also 0 for hours available.
So I guess I should be using ALL with FILTER in the denominator, but I'm not sure how to access the dates chosen by user.
What do you guys suggest?
Check the filter options for the relationship between WorkDone and your Employee table.
The default behavior (filter goes bothways) can be changed when you edit the relationship.
The cross table direction is already set to single, and btw, i do need filters to propagate for other parts of the page; it's just the measure calculation that is an exception.
Can you post a screenshot of your model?
