DAX Measure that applies a filter to a field already filtered

[tonylee]

I have a table for people who have applied for, been accepted for, and attended a thing. I started with...

Applications = COUNTROWS ( Interactions )

and then I did this...

Places Allocated =

CALCULATE ( [Applications], NOT ( ISBLANK ( Interactions[Attended] ) ) )

and places allocated gives me 198 people.

I finally did this...

Attended = CALCULATE ( [Places Allocated], Interactions[Attended] = TRUE() )

which also gave me 198 people. If I change this to either of...

Attended = CALCULATE ( [Applications], Interactions[Attended] = TRUE() )

or

Attended = CALCULATE ( [Applications], NOT ( ISBLANK ( Interactions[Attended] ) ), Interactions[Attended] = TRUE() )

I get 137 people, which is what I want.

I clearly have an answer but I don't understand why my first attempt at [Attended] didn't work, when I think it's essentially what I have in my third attempt - but clearly I am wrong.

Thanks

[AntrikshSharma · Accepted solution]

2 filters inside the same CALCULATE are applied as an AND condition, in case of 2 filters inside nested CALCULATE the filter context generated by inner CALCULATE will overwrite the filter context generated by outer CALCULATE.

Your outer CALCULATE generated a filter context where Interactions[Attended] = TRUE() but the inner CALCULATE generates the filter context where Interactions[Attended] is not blank and this is why the inner CALCUALTE nullifies the effect of the filter context generated by the outer CALCULATE and at the end you get the result for Attended not blank

Attended =
CALCULATE (
    CALCULATE (
        [Applications],
        NOT ( ISBLANK ( Interactions[Attended] ) ) -- This overwrites the Filter context 
                                                   -- generated by outer CALCULATE on the same 
                                                   -- column
    ),
    Interactions[Attended] = TRUE () -- The filter context generated here is overwritted by the 
                                     -- filter context create by the inner CALCULATE
)

[amitchandak]

@tonylee , try if this gives you 137

Attended = CALCULATE ( [Places Allocated], filter(Interactions,Interactions[Attended] = TRUE() ))

if so, overlap filter can be an issue.