Drill through with Calculation Groups that change Date Context

Thanks! 

So, I have learnt the following things.

- Calculation groups for time intelligence do not filter the underlying table - only the measure. Calculation groups are only measure filters.
- A union table with both latest tickets is the best option, that is further filtered to the latest date er ticket ID, rather than per status as this still produces duplicate values across more than one snapshot period. I used UNION, FILTER, EARLIER and RANKX to achieve this, and made sure to create a column with a tag specifying which data table the row came from.

VAR BaseTable =
     UNION ( Res , Unres )

VAR RankedTable =
     ADDCOLUMNS (
          BaseTable ,
               "Ranked" , RANKX (
                                     FILTER (
                                          BaseTable ,
                                          [TicketID] = EARLIER ( [TicketID] ) ) ,
                                    [Last Updated DateTime] , ,
                                    DESC ,
                                    DENSE ) )
RETURN
     FILTER (
          RankedTable ,
          [Ranked] = 1 )

- Building all measures from this table (i.e. Load ), and then referencing that measure for child measures returns the best and fastest drill-through and visual rendering. For example,

Resolved =
     CALCULATE (
          [Load] ,
          fctTickets[LoadType] = "Resolved" )

The drill-through was a bit trickier. 

The client requested the capacity to drill-through on any data point; KPI, matrix, scatter chart, column chart, line chart, you name it. So we needed to drill on measures. In addition, he also wanted to see just one value as a page (i.e. Ticket Details) when they hovered/right-clicked to drill-through on a data point.

This made what could have been a quick task a mammoth trial and error, that took me two months to figure out. 

1. Create a dimVisual datatable, wherein you list all your report pages.
2. Create a dt_[VisualName] datatable wherein each column contains the display names of your measures used, and has its own related ordinal table. 

I did it this way and not one column with all the measures I'd use on the report page because there is a unique order to the columns/bars/any metrics in the visuals. For example, If [% Unresolved Tickets] or [% Tickets OSLA] were listed before [Load] then the reader flow from left to right would make no sense. Percentage unresolved of how many tickets, exactly? 

The ordinal columns per visual column dictate the order of the measures per visual. This is necessary as you cannot have more than one value for the same ordinal value. This is why all blank values are 99 for [Ordinal R] , as opposed to:

BLANK ( ) | 6
BLANK ( ) | 7
BLANK ( ) | 8

PBI cannot return a scalar value (one resultant value) if there are multiple values for the filter. As above, BLANK () would return 6 , 7 and 8 and would break the calculation.

3. Create a switch statement for each column in the dimVisual_ table. 

switch_RequesterAnalysis_Summary =
     SWITCH (
          SELECTEDVALUE (
               dt_RequesterAnalysis[RequesterAnalysis_Summary] ) ,
                    "Average Unresolved Ticket Age" , [Unresolved Ticket Age Avg] ,
                    "Average First Resolution Time" , [FR Response Time Avg] ,
                    "First Responses Unresolved/First Responses" , [FR Unresolved/FR] )

4. Create a master switch statement. This will go on the drill-through details visual as a column/visual filter.

master_SwitchStatement =
     SWITCH (
          SELECTEDVALUE ( dimVisual[VisualName] ) ,
               "RequesterAnalysis_Summary" , [switch_RequesterAnalysis_Summary] ,
               "RequesterAnalysis_Metrics" , [switch_RequesterAnalysis_Metrics] )

Once all that is set up, you can built it the visuals and the drill-through details page.

Report Page >Visual > Value > [switch_RequesterAnalysis_Summary]

Report Page > Visual > Columns > dt_RequesterAnalysis[RequesterAnalysis_Summary]

Visual > Filter Pane > Filters on this visual > dimVisual[VisualName] = "RequesterAnalysis_Summary"

Drill-through page > table visual > [master_SwitchStatement], fctTickets[TicketID]

Drill-through page > table visual > filter pane > filters on this visual > master_SwitchStatement = "1"

Drill-through page > drill-through fields > [master_SwitchStatement], [switch_RequesterAnalysis_Summary], 'dt_RequesterAnalysis'[RequesterAnalysis_Summary] , dimVisual[VisualName]

So here's what happens.

1. When you place 'dt_RequesterAnalysis'[RequesterAnalysis_Summary] in the visual's column well, the visual shows all the rows in that column as separate columns > these are the measure names.

2. When you place [switch_RequesterAnalysis_Summary]  in the values well of that same visual, the column header value returns the measure you specified in the switch statement. 

So, PBI goes, okay this cell is filtered by the value "First Responses Unresolved/First Responses" in dt_RequesterAnalysis[RequesterAnalysis_Summary], and the value I'm supposed to return in this cell is, according to [switch_RequesterAnalysis_Summary], [FR Unresolved/FR].

That single cell has the following filters
- dimVisual[VisualName] = "RequesterAnalysis_Summary"
- dt_RequesterAnalysis_Summary[RequesterAnalysis_Summary] = "First Responses Unresolved/First Responses"

When you drill-through to the details table, these two filters are carried to the page.

So, [master_SwitchStatement] - the only measure on the details table - goes, when dimVisual[VisualName] = "RequesterAnalysis_Summary", I'm supposed to return [switch_RequesterAnalysis_Summary].

Now master_SwitchStatement is actually [switch_RequesterAnalysis_Summary], which when placed in the same visual with [TicketID], returns only the ticket ids (rows) that contribute to the measure [FR Unresolved/FR], because master_SwitchStatement is filtered to "1" in the visuals filter pane.