DETAILROWS DAX Function (Table manipulation) Context Transition
Returns the table data corresponding to the DetailRows expression defined on the specified Measure. If a DetailRows expression is not defined then the entire table to which the Measure belongs is returned.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Measure |
A Measure reference whose DetailRows expression is to be evaluated. |
Return values
Remarks
IMPORTANT : DETAILROWS should perform a context transition, but in SSAS 2017 it does not do that. The behavior by design is to perform a context transition, and this should be fixed in upcoming hotfix or service packs. As a workaround, if called in a row context, it should be wrapped in a CALCULATETABLE statement. Do not use in a row context if the intended behavior should not execute the context transition – assign the result of DETAILROWS in a variable before the iterator in that case.
Examples
Consider the following Detail Rows Expression in the Sales Amount measure:
SELECTCOLUMNS (
Sales,
"Order Number", Sales[Order Number],
"Order Line Number", Sales[Order Line Number],
"Customer", RELATED ( Customer[Name] ),
"Product", RELATED ( 'Product'[Product Name] ),
"Quantity", Sales[Quantity],
"Line Amount", Sales[Quantity] * Sales[Net Price]
)
DETAILSROWS invokes the Detail Rows Expression for the corresponding measure in the same filter context.
-- Show DETAILROWS for transactions made in one day
EVALUATE
CALCULATETABLE (
DETAILROWS ( [Sales Amount] ),
'Date'[Date] = DATE ( 2007, 9, 19 ),
Customer[Customer Type] = "Person"
)
| Order Number | Order Line Number | Customer | Product | Quantity | Line Amount |
|---|---|---|---|---|---|
| 20070919123935 | 1 | Barnes, Alexis | Contoso 512MB MP3 Player E51 Blue | 1 | 11.69 |
| 20070919123937 | 1 | Ruiz, Eddie | Contoso 512MB MP3 Player E51 Blue | 1 | 11.69 |
| 20070919123941 | 1 | Anderson, Eduardo | Contoso 512MB MP3 Player E51 Blue | 1 | 11.69 |
| 20070919427828 | 1 | Gao, Ernest | Fabrikam Social Videographer 2/3” 17mm E100 Grey | 1 | 144.00 |
| 20070919712464 | 1 | Cook, Jared | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919712465 | 1 | Patel, Cassandra | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919712466 | 1 | He, Willie | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919712467 | 1 | Jones, Jennifer | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919525616 | 1 | Rubio, Jésus | The Phone Company Touch Screen Phones – CRT M11 Grey | 1 | 170.10 |
| 20070919525617 | 1 | Shan, Leonard | The Phone Company Touch Screen Phones – CRT M11 Grey | 1 | 170.10 |
| 20070919525618 | 1 | Hernandez, Albert | The Phone Company Touch Screen Phones – CRT M11 Grey | 1 | 170.10 |
| 20070919123936 | 1 | Vazquez, Monique | Contoso 512MB MP3 Player E51 Blue | 4 | 46.76 |
| 20070919525615 | 1 | Hall, Destiny | The Phone Company Touch Screen Phones – CRT M11 Grey | 4 | 680.40 |
Related articles
Learn more about DETAILROWS in the following articles:
-
Controlling drillthrough using Detail Rows Expressions in DAX
The Detail Rows Expression in a Tabular model provides the user with control over the drillthrough results obtained by showing details of a measure. This article describes typical DAX expressions you can use in this property. » Read more
-
Creating table functions in DAX using DETAILROWS
This article describes how to use the detail rows expression of a measure to obtain the equivalent of creating table functions in DAX. This allows the reusing of a table expression in multiple CALCULATE filters. » Read more
Last update: Mar 5, 2021 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo