Forum Discussion

Arminx's avatar
Arminx
Frequent Visitor
9 years ago
Solved

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 ...
  • Arminx's avatar
    Arminx
    9 years ago

    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.

     

Arminx's avatar
Arminx
Frequent Visitor
9 years ago

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?