ALLEXCEPT Behavior - Appears To Retain Some Filters

[kbol]

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?

[v-kelly-msft]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Well, that might be because your All Time measure is adding them back in, what is the formula for that measure?

[kbol]

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

[amitchandak]

In case it is not resolved, refer

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

[kbol]

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?

[kbol · Accepted solution]

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.