cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Arminx
Frequent Visitor

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?





1 ACCEPTED SOLUTION
Arminx
Frequent Visitor

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.

 

View solution in original post

10 REPLIES 10
LaurentCouartou
Solution Supplier
Solution Supplier

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?

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

 

 

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

@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

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
Frequent Visitor

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

 

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

 

Best Regards,

Herbert

Arminx
Frequent Visitor

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?

 

 

 

 

 

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.