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