DAX Expanded Table Filtering Requiring two CALCULATETABLE statements

[bcdobbs]

Hi,
I have a data model as shown here:

There is a basic measure that counts number of tickets:

Number of Tickets = COUNTROWS(Tickets)

I wrote a measure that works fine to calculate the average number of tickets per event.
Requirement was that the denominator of the average (number of events) needed to only respond to filters on organiser, venue and event. Eg if a filter was applied to TicketClass or TicketStatus the number of events remained unaffected.

VAR EventIdTable =
    CALCULATETABLE (
        VALUES ( Events[EventId] ),
        CROSSFILTER ( Events[EventId], Tickets[EventId], BOTH ),
        ALLEXCEPT ( Tickets, Events, Organisers, Venues )
    )
VAR Result =
    AVERAGEX (
        EventIdTable,
        IF.EAGER ( ISBLANK ( [Number of Tickets] ), 0, [Number of Tickets] )
    )
RETURN
    Result

However having read the chapter on expanded tables in the definitive guide to DAX I tried the following:

VAR EventIdTable =
    CALCULATETABLE (
        VALUES ( Events[EventId] ),
        Tickets,
        ALLEXCEPT ( Tickets, Events, Organisers, Venues )
    )
VAR Result =
    AVERAGEX (
        EventIdTable,
        IF.EAGER ( ISBLANK ( [Number of Tickets] ), 0, [Number of Tickets] )
    )
RETURN
    Result

Having played with it in DAX studio it became clear that ALLEXCEPT wasn't removing filters from TicketClass or TicketStatus tables.  I was able to make it work putting a second CALCUALTETABLE in as follows:

VAR EventIdTable =
    CALCULATETABLE (
        CALCULATETABLE ( VALUES ( Events[EventId] ), Tickets ),
        ALLEXCEPT ( Tickets, Events, Organisers, Venues )
    )
VAR Result =
    AVERAGEX (
        EventIdTable,
        IF.EAGER ( ISBLANK ( [Number of Tickets] ), 0, [Number of Tickets] )
    )
RETURN
    Result

Hoping someone can help explain what is happening with the filter contexts here that requires an outer CALCULATETABLE.

Thanks

Ben

[AlB · Accepted solution]

Hi @bcdobbs 

Can you share the pbix?

In the first version:

VAR EventIdTable =
    CALCULATETABLE (
          VALUES ( Events[EventId] ),
          Tickets,
          ALLEXCEPT ( Tickets, Events, Organisers, Venues )
)

Tickets carries your filter context, including filters on TicketClass and TicketStatus. Even though your last argument clears those TicketClass, TicketStatus filters, you're throwing them right back in in your second argument. Remember that every filter argument is evaluated in the original filter context independently (i.e. it is not affected by any other modifier or filter argument within that Calculate). Then the new filter context is built as an AND of all the filter arguments. This is the crux to the behaviour you are seeing.

In the second version, you're feeding the inner calculate with a filter context that has TicketClass, TicketStatus cleared. 

 VAR EventIdTable =
CALCULATETABLE (
       CALCULATETABLE ( VALUES ( Events[EventId] ), Tickets ),
       ALLEXCEPT ( Tickets, Events, Organisers, Venues )
)

Tickets thus has, as a result, TicketClass and TicketStatus cleared. It is then applied to the first argument in the inner calculate.  

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[bcdobbs]

That makes perfect sense. Simple when you put it like that! Thank you.

Simplified version of the PBIX file attached (orginal was running direct on EventBrite API so had to cut it down:
PBIX Example