Forum Discussion

kbol's avatar
kbol
Helper I
6 years ago
Solved

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...
Need Help
  • kbol's avatar
    kbol
    6 years ago

    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. 

v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

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!