Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


How to Define Drillthrough in Analysis Services Tabular 2017

By:   |   Read Comments   |   Related Tips: > Analysis Services Development

Identify and Eliminate SQL Server Performance Monitoring Issues - Free Webcast


I have a PivotTable in Excel where I show users data from an Analysis Services Tabular 2017 model. The PivotTable shows aggregated data and users were wondering how the data was calculated. I’m looking for behavior like a drillthrough report in Reporting Services. Is this possible?


Let’s illustrate the problem with an example. Suppose we have the following PivotTable in Excel:

excel pivottable

Users are wondering who exactly those 8 customers are from the External region. You can either add extra fields to the PivotTable with more descriptive information about the customers, but this severely changes the layout of the PivotTable and it might not contribute to the original analysis. Another option is to double-click on the cell containing the number 8 or right-clicking the cell and choosing Show Details.

show details pivottable cell

This will open a new worksheet in Excel that will retrieve the first 1,000 rows that contribute to this number. In our case, it’s all the columns of the Customers table for those 8 customers:


The problem with this default drillthrough is all columns are returned – even the Customer Key although it is hidden from client tools in the Tabular model – and they have quite unfriendly column names. The columns are also returned in the order as they are defined in the model. We would like a more granular control over which data is returned by the drillthrough. Luckily this is what the new compatibility level 1400 of SQL Server Analysis Services 2017 (SSAS) gives us: full control.

Test Set-up

In this tip, we’ll use a Tabular model built open the WideWorldImporters data warehouse sample database. You can find more information on how to install the sample database in the tip Install SQL Server 2016 Sample Database: Wide World Importers Data Warehouse and you can download the Tabular model here.

The Detail Rows Property in SSAS 2017

The solution comes in the format of a property you can set on individual measures or on tables: the Detail Rows Expression.

detail rows property for a measure

With this property you can define a DAX query that will be executed whenever a drillthrough is requested.  You can define one for every measure, or you can define a default Details Rows expression on the table. This default expression will be used for all measures that don’t have the property set.

Let’s define a detail rows expression for the Total Sales measure in the Order table. Clicking on the ellipsis for the property will open up a DAX editor. With the SELECTCOLUMNS function we can specify the columns we want to retrieve and change the column names as well:

specify detail rows expression

By using the RELATED function, we can also retrieve columns from other tables, as long as there’s a relationship between the tables. Let’s also define a similar expression for the Order table:

default details rows expression

Let’s try this in Excel. We have the following PivotTable:

PivotTable bis

We are interested in more details for the External territories for the year 2016. Double-clicking on the cell opens the following sheet:

order id

Keep in mind the detail rows expression can return many rows when it is executed on a low grain of the data. Excel will show only the first 1,000 rows, but at the server the whole expression is evaluated. If you have a very large fact table, this can have a considerable performance impact, especially if context transitions are used in the DAX expression.

Let’s test the default detail rows expression on the Order table by switching the Total Sales measure with the OrderCount measure:

pivottable tres

Double-clicking on a cell now gives the following drillthrough:

default detail rows expression result

The DetailRows Function

To accompany the new Detail Rows property, there’s also a new function in DAX: DETAILROWS (there’s no documentation page yet at the time of writing). This function allows you to retrieve the row set defined by the Detail Rows expression, which is similar to the DRILLTHROUGH statement in MDX. Since it returns a table, you can use it in any DAX function that expects a table as parameter.

An example of the function to retrieve the details for the Order table:

using the DETAILROWS function

If we evaluate this function for a measure which doesn’t have the detail rows property set and there’s no default specified for the parent table, the whole table is returned:

detailrows with no property specified

Do not try this at home (or at your production server) for very large tables.

Drillthrough in Other Tools

The Detail Rows property is supported very well in Excel, but what about other tools? In Reporting Services, the ability to create drillthrough reports has always been there. You specify an action on a report item and you configure the action to go to another report, with the ability to pass along parameter values. You can either specify a whole new DAX query, or use the DETAILROWS function. You can learn more about SSRS drillthrough reports in the tip SQL Server Reporting Services 2012 Drilldown Features.

In Power BI Desktop, you can mimic the behavior of drillthrough reports using the bookmarks feature. You can also use the DETAILROWS function in the query editor along with parameters, as explained in the blog post Query Detail Rows In Power BI.

Analysis Services Multidimensional has a similar feature as the Detail Rows expression: the drillthrough action. It also allows you to specify which columns are returned when you request a drillthrough on a cell. But it’s hard to control the order of the columns and their column names. In Tabular you have absolute freedom over the query that returns the table. You can learn more about the feature in the tip Enabling Drillthrough in Analysis Services.


With the new Detail Rows Expression property and the DETAILROWS function we have full control over the drillthrough experience in Analysis Services Tabular 2017. This new functionality is available in the 1400 compatibility level.

Next Steps

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Send me SQL tips:


Learn more about SQL Server tools