DAX Expanded Table Filtering Requiring two CALCULATETABLE statements
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
