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

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. 

LaurentCouartou's avatar
LaurentCouartou
Icon for Solution Supplier rankSolution Supplier
9 years ago

Can you post a screenshot of your model?

  • 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
      Icon for Microsoft Employee rankMicrosoft 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. 

         

         

         

    • LaurentCouartou's avatar
      LaurentCouartou
      Icon for Solution Supplier rankSolution Supplier
      9 years ago

      From what I can see, filters on the Tornado table do not propagate to the 'public employee' table. However, they propagate to the 'public project' table.

      Is there any chance 'public project' indirectly filters the 'public employee_schedule'? This might be the cause of the observed behavior.

       

      Anyway, taking the DAX route, you can try someting along the line:

      TotalAvailableHours:=CALCULATE(
              SUM( 'public employee_schedule'[hours])
              , ALL(Tornado)
      )