Forum Discussion
ALLEXCEPT Behavior - Appears To Retain Some Filters
I did arrive at a solution. I don't know if this is the most idiomatic approach, but should anyone else stumble upon this thread later, here's what worked for me:
Time Pct = VAR DateStart = FIRSTDATE(DateCal[Date]) VAR DateEnd = LASTDATE(DateCal[Date]) VAR TotalTime = CALCULATE( [All Time], ALL(Timesheet), Timesheet[EntryDate] >= DateStart, Timesheet[EntryDate] <= DateEnd ) RETURN DIVIDE( [All Time], TotalTime )Note that I'm now slicing on a general Date calendar that has a relationship to Timesheet on entry date.
[All Time] is a sum of several measures all taking the form:
X Time = CALCULATE(
SUM(Timesheet[Hours]),
FILTER(
Timesheet,
(Timesheet[TimeType] = "XTypeString1" || Timesheet[TimeType] = "XTypeString2")
)
)
Some of the measures apply additional filter criteria to the Timesheet entries, but they do this only by checking other fields on the row. I think it would be much more appropriate and flexible to label each entry in a calculated column and use [All Time] as a simple sum of hours, slicing on the calculated column. My plan is to do so once I get to refactor this report proper, but for now I've been vexed by troubles with this measure.
Thanks for the help so far.
Using ALL does not quite create the desired measure. I want to retain the overall date selection; there is a date range slicer on EntryDate and TotalTime should yield the sum of hours over this selected period.
I do understand the problem a bit better now. While the measure would never be viewed this way, if I place the EntryDate and TimeLabel fields and the Denominator measure (TotalTime) with two days selected, I get values like:EntryDate TimeLabel Denominator (TotalTime) 1/6/2018 D 1.00 1/5/2018 A 359.00 1/5/2018 B 359.00 1/5/2018 C 359.00 1/5/2018 D 359.00 ... and so on. In fact, only first row has 1.00 for Denominator and all the others have 359. The correct value is 360. I need for the measure to evaluate to 360 for each row here, but not to the grand total of several hundred thousand hours across all Timesheet entries.
This need sounds a bit like ALLSELECTED on Timesheet[EntryDate] to me, but on its own this approach leads to the measure calculating subtotals for combinations of EntryDate + any other field. How can I modify this measure to produce the sum of hours in the entire selected period?I did arrive at a solution. I don't know if this is the most idiomatic approach, but should anyone else stumble upon this thread later, here's what worked for me:
Time Pct = VAR DateStart = FIRSTDATE(DateCal[Date]) VAR DateEnd = LASTDATE(DateCal[Date]) VAR TotalTime = CALCULATE( [All Time], ALL(Timesheet), Timesheet[EntryDate] >= DateStart, Timesheet[EntryDate] <= DateEnd ) RETURN DIVIDE( [All Time], TotalTime )Note that I'm now slicing on a general Date calendar that has a relationship to Timesheet on entry date.