Forum Discussion
ALLEXCEPT Behavior - Appears To Retain Some Filters
Hi,
I've got a table of timesheet entries, each with an entry date, amount of hours, and several fields I want to slice on such as the type of time. I'm trying to write a measure that compares the amount of time with the selected criteria to all time within the selected range of entry dates - in other words, of all the time in the current date range, what percent has the sliced values?
My DAX is this:
Time Pct = VAR
TotalTime = CALCULATE(
[All Time],
ALLEXCEPT(Timesheet, Timesheet[EntryDate])
)
RETURN
DIVIDE(
[All Time],
TotalTime
)
where [All Time] is a previously-defined measure that sums different types of timesheet entries. Unfortunately, ALLEXCEPT does not seem to be removing all filters.
I have a field called "timesheet group" on the Timesheet table with values [A-E]. I've separated out the numerator and denominator of Time Pct into separate measures. Without making any selections, the denominator - TotalTime - is correct at 360 for a specific date range. However, when I select a "timesheet group", it decreases to 359 for all values except for "D" (presumably the value of the offending hour?). I would have thought that ALLEXCEPT would cause the measure's calculation to ignore these selections.
Could someone help me find my misunderstanding?
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.
6 Replies
Well, that might be because your All Time measure is adding them back in, what is the formula for that measure?
[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.
Hi kbol ,
I singled out your measure of totaltime and you will see what misunderstood you:
If you put the measure together with the column of entry date in a table visual,you will see:
But when you put the measure in a card visual,you will see:
So you will find that your measure doesnt remove all the filters.If you want to remove all the filters,you'd better use the function "all" instead of "all except".
And your measure needs to be corrected to below：
Time Pct = VAR TotalTime = CALCULATE( [All Time], ALL(Timesheet) ) RETURN DIVIDE( [All Time], TotalTime )Best Regards,
KellyDid I answer your question? Mark my post as a solution!
