Unexpected Tabular Drillthrough results for cell

Jun 9, 2014 at 3:47 PM
I tried setting up a drillthrough action on a tabular model. It appears to return results but not work as expected. When clicking on show details for a cell it does not appear to only return details that only pertain to that cell. I am using Excel 2013 to view the data.

Settings are as follows:
  1. ActionType = DrillThrough
  2. TargetType = Cells
  3. Target = MeasureGroupMeasures("TableName")
  4. Condition = blank
  5. Drillthrough Columns = Columns needing displayed
  6. Default = True
  7. MaximumRows = blank
  8. Invocation = Interactive
  9. Perspectives = non checked
Coordinator
Jun 9, 2014 at 4:04 PM
Which measure are you double clicking on? Let's say you double clicked on "Sales Last Year" cell while filtered to 2014. It's going to show you 2014 sales in the drillthrough even though your Sales Last Year calculation is showing 2013 sales. Unfortunately that's how drillthrough works in Tabular. It doesn't pay attention to the internals of the calculated measure you're drilling through on.
Jun 9, 2014 at 4:24 PM
I have a measure group called "Financials". The pivot table has a measure "# with Financials" that is part of the "Financials" measure group. I click on the cell that contains the measure value. The Row Labels are Company. The values are listed in the column headers. And I have 3 other filters on the entire pivot table not used as row or header labels. I would expect if the value is 2 then there would be 2 rows underlying that data as details.
Coordinator
Jun 9, 2014 at 4:36 PM
What's the DAX for the "# with Financials" measure?
Jun 9, 2014 at 4:39 PM
Edited Jun 9, 2014 at 5:07 PM
CALCULATE(
        DISTINCTCOUNT('Financials'[ID]),
                'Financials'[HasFinancials]
    )
Coordinator
Jun 9, 2014 at 4:49 PM
If your measure is a distinct count, then even though you double click on a number that says 2, there might be 200 rows underneath it with only 2 distinct values. Correct? Drillthrough just shows all the rows in that table in the current filter context for that cell you double click.

If you are wanting to just see the two distinct values, you may need to build a rowset action and define your own query that returns only the two IDs.
Jun 9, 2014 at 4:55 PM
Edited Jun 9, 2014 at 5:07 PM
That is true. However, the underlying data has only 2 rows of data not 200. DistinctCount is in there as a sanity check.

It returns results details for both measures.
WithFinancials:=DISTINCTCOUNT('Financials'[ID]),
            'Financials'[HasFinancials]
)
Without Financials:+DISTINCTCOUNT('Financials'[ID]),
            'Financials'[HasFinancials] = False()
)
When clicking on the Without Financials cell value it returns details for with financials and without financials.
Coordinator
Jun 9, 2014 at 5:01 PM
Correct. Drillthrough doesn't pay attention to the internals of the calculated measure. You will have to move that filter to the PivotTable filters. Or you will have to build a RowSet action that includes that filter in the query defined in that RowSet action.
Marked as answer by dumstattd on 6/9/2014 at 9:05 AM