Forum Discussion
Use filter but avoid propoagation effect
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.
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.
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.
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.
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)
)
