My second-favourite feature in SSAS Tabular v.next 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:
[sourcecode language=”text” padlinenumbers=”true”]
Total Sales:=SUM(Sales[Sales])
Total YTD Sales:=TOTALYTD([Total Sales], ‘Date'[Date])
[/sourcecode]
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 v.next 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:
[sourcecode language=”text”]
SELECTCOLUMNS(
‘Sales’,
"Date", ‘Sales'[Date],
"Sales Value", [Total Sales]
)
[/sourcecode]
[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:
[sourcecode language=”text”]
CALCULATETABLE(
SELECTCOLUMNS(
‘Sales’,
"Date",
‘Sales'[Date],
"Sales Value",
[Total Sales]
),
DATESYTD(‘Date'[Date])
)
[/sourcecode]
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.
Hi Chris
Good article thanks.
Do you know if this feature is in Power BI?
Not yet – I hope it will appear in Power BI eventually though.
It’s never too late. I asked this feature in 2005 (just in talks at conferences), formally in 2009 (https://connect.microsoft.com/SQLServer/Feedback/Details/403083) and finally we have this (in Tabular and not in Multidimensional).
Never give up!
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….
Wow… I never thought I’d see the day!!
Exposing this feature in Power BI (service and report server) is really needed along with the inclusion of an actual working pivot table visual.
Well I cant get it to work. When I define a simple Detail Row expression like this one:
SELECTCOLUMNS(‘Revenue’,”dataset”,Revenue[DataSource])
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.
Chris,
Do you know if this new feature will show up when you right-click on a cell that uses the CUBEVALUE formula in Excel and go to Additional Actions?
Screenshot: https://i.imgur.com/kDRlsr6.png
No, I wouldn’t expect this to show up on a cell containing a CUBEVALUE formula.
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?
Have you got the latest version of SSDT installed? What compatibility level is your SSAS database set to?
Thanks for pointing at the right direction
the compatability mode was set to sql server 2016
with that changed i have this attribute avaliable!
Any chance you can refer the default detail rows expression you set at table level, in your Measures?