Power Pivot Principles: The A to Z of DAX Functions – DETAILROWS

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at DETAILROWS.

The DETAILROWS function

The DETAILROWS
returns the table data corresponding to the DETAILROWS expression defined on the specific Measure.  Known as a “Detail Rows Expression”, this defines the details shown when the user double-clicks on a value in a PivotTable.  The syntax of DETAILROWS is:

It has one [1] argument in the syntax:

• [Measure]: this is required and is any appropriate DAX measure.

DETAILROWS returns a table containing the data generated by the Detail Rows Expression.  If no Detail Rows Expression is defined, the data for the table containing the measure is returned. 

Clear as mud, we know.  The best way to explain this is with an example.  We have created a measure Sales, which is based on columns in Actuals, and is housed in Actuals:

If we create a PivotTable with Sales as the value:

We may double-click on any value to drill down and we will see all the data from Actuals, not just the columns used to create Sales.

If we create a Detail Rows expression for the measure Sales with only the columns used to create it (shown here in the Tabular Editor):

Then drilling down on Sales will show these columns instead, which is more useful for the user:

DETAILROWS can be used to extract the data shown when we double-click on Sales in the PivotTable (shown here in Power BI):

If no Detail Rows expression has been defined for the measure, then DETAILROWS will return the columns of the table containing the measure, as shown here for Sales cash receipts (using a Power BI screenshot):

If we moved Sales cash receipts to another table, then the columns from the new table would appear, even though none of the columns would be used to create the measure. 

DETAILROWS can be referenced by other DAX functions that work with tables, but the output will vary according to whether a Detail Rows expression has been set up for the measure concerned.

 Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.