More Detail On Detail Rows Expressions In SSAS Tabular V.Next

My second-favourite feature in SSAS Tabular after Power Query integration is the Detail Rows expression property for measures – it not only brings drillthrough on measures to Tabular, it means that we can define meaningful drillthrough on any measure, no matter how it is calculated. There’s a basic description of the functionality in this blog post but I thought it would be useful to walk through a simple example showing how it can be used.

Consider a simple SSAS Tabular model with two tables in it. First, a table containing sales data called Sales:


Second, a date table called Date:


[Note: dates are in DD/MM/YYYY format]

There are two measures with the following definitions:

Total Sales:=SUM(Sales[Sales])

Total YTD Sales:=TOTALYTD([Total Sales], 'Date'[Date])

The measure Total YTD Sales gives the running total of sales from the beginning of the current year. I know it doesn’t follow Marco and Alberto’s best-practice pattern but I wanted to keep things simple on the DAX front…

Browsing the model in an Excel PivotTable gives the following result:


At this point if you double-click on cell C6 in the PivotTable you get the following, not very useful result, on a new worksheet:


What drillthrough does in SSAS Multidimensional, and what the new Detail Rows Expression property in SSAS Tabular does, is allow an end user to see the detail-level data (usually the rows in the fact table) that was aggregated to give the value the user clicked on in the original PivotTable.

For the Total Sales measure, this property can be set with a DAX expression that returns a table something like this:

	"Date", 'Sales'[Date], 
	"Sales Value", [Total Sales]

[For more details on the SelectColumns() function, see here]


Now when you click on cell C6 in the PivotTable you get the result of the table expression above filtered by the context of the cell you’ve clicked on – in this case, the date 4/1/2017. What appears in the new worksheet is data from the row from the Sales table for 4/1/2017:


This is already better than SSAS Multidimensional drillthrough because as a developer you have control over the column headers displayed in this table (in Multidimensional drillthrough the column names come out in a ridiculously user-unfriendly format) and the order that they are displayed in (which is equally painful to control in Multidimensional).

Now, consider cell D6 in the PivotTable, the cell that shows the year-to-date sales amount for 4/1/2017. If a user double-clicked on this cell they would expect to see all of the rows from the Sales table from 1/1/2017 to 4/1/2017, the rows whose sales have been aggregate to give the YTD total.

This can be achieved using the following expression in the Detail Rows Expression for the Total YTD Sales measure:

		"Sales Value", 
		[Total Sales]


Now, double-clicking on cell D6 in the PivotTable gives the following table:


It may not look all that impressive, but there are few words that can describe how happy this makes me feel. This is exactly what is not possible with drillthrough in SSAS Multidimensional, and why drillthrough in Multidimensional has always been so frustrating to use. It’s great to see the feature properly implemented in Tabular.

18 responses

  1. Pingback: Dew Drop - January 17, 2017 (#2404) - Morning Dew

  2. I never understood how MS could call it BI for the MASSES without such a critical feature in such a critical product for such a tremendously long period of time–especially with calculated members! What had to be done before was just unbelievably painful, and kudos to Darren G for leaving his solution up on MS Connect for so many years!

    We all need to yell more loudly….

  3. Pingback: Drillthrough On Calculated Members In SSAS MD 2017 Using DAX Expressions, Part 1 – Chris Webb's BI Blog

  4. Exposing this feature in Power BI (service and report server) is really needed along with the inclusion of an actual working pivot table visual.

  5. Well I cant get it to work. When I define a simple Detail Row expression like this one:


    on a measure that just sums a column from that particular table I get

    RETURN clause cannot be used with the DRILLTHROUGH statement when a detailrows expression is already defined on the measure ‘Revenue’

    What am I doing wrong?

    • Any luck with this error? I’m having the same problem. I’m currently going through Microsoft Support and their engineer can’t figure this out at the moment either.

    • Gabriel – I did find the root cause of this error. It is the version of excel you are using. So if you are currently running MSO version of office and all of your updates are installed, the detail rows feature will not work. You can double check by installing the Click2Run version of Microsoft Office – detail rows works fine in this version. A fix for this detail rows error is likely coming sometime in January, from what I’ve been told by the Microsoft support engineer.

  6. Pingback: Making Power BI Drillthrough Return The “Right” Rows When You Use It With Complex Measures « Chris Webb's BI Blog

  7. Hi
    For some reason i dont see this property when on my measure on ssas tabular in sql server 2017
    All i have in the Advanced node is “Display Folder”
    Any ideas why?

      • Thanks for pointing at the right direction
        the compatability mode was set to sql server 2016
        with that changed i have this attribute avaliable!

Leave a Reply to Gabriel Tasiopoulos Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: