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

DB Schema. 'Tornado' is the table that records hours spent. employee_schedule is the table that records available hours per day per employee.

 

 

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

Arminx

 

Which column do you select in the timeline control? If you want to use ALL with FILTER in the denominator, you can try to access the dates chosen with the DAX measure like below. You can replace "WorkDone" in MinDate & MaxDate with the acutal table/column you used for the timeline control.

 

productivity = 
VAR MinDate =
    CALCULATE ( MIN ( WorkDone[date] ), ALLSELECTED ( WorkDone ) )
VAR MaxDate =
    CALCULATE ( MAX ( WorkDone[date] ), ALLSELECTED ( WorkDone ) )
RETURN
    (
        SUM ( WorkDone[hours] )
            / CALCULATE (
                SUM ( HoursAvailable[available_hours] ),
                FILTER (
                    ALL ( HoursAvailable ),
                    HoursAvailable[date] >= MinDate
                        && HoursAvailable[date] <= MaxDate
                )
            )
    )

 

Best Regards,

Herbert

  • Arminx's avatar
    Arminx
    Frequent Visitor
    9 years ago

    Hey thanks v-haibl-msft! This definitely sounds like in the right direction. 

     

    The timeline control is based on WorkDone[date].

     

     

    Based on your formula, MinDate and MaxDate are calculated correctly, but the CALCULATE part doesn't return the right sum of hours which i otherwise would get from a select on my database. It's really off the range like 40 hours when a single month is chosen, which should be 21*8=168  Really out of clue .. 

     

     

     

     

     

    I tried using SUMX instead of CALCULATE to see if something wrong with the data; it would return the right sum, but it ignores the other slicer that I have for employee_id; thus calculating hours for all employees and not the chosen employee. 

     

    SUMX ( 
    FILTER (ALL ( HoursAvailable ),
HoursAvailable[date] >= MinDate
&& HoursAvailable[date] <= MaxDate
), 

    HoursAvailable[available_hours])

     

    p.s. if i don't choose any employees the CALCULATE function returns wrong values. even if i choose an employee it simply ignores it without any updates. 

     

     

     

    • Arminx's avatar
      Arminx
      Frequent Visitor
      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.

       

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        Arminx

         

        If you have got your problem solved, you could mark the right answer to close this thread. :smileyhappy:

         

        Best Regards,

        Herbert