Forum Discussion

CrassJoin's avatar
CrassJoin
Advocate I
7 years ago

SSAS Tabular - Default Detail Rows Expression

For a SSAS 2017 tabular model surfaced via pivot tables, a handy (vital) feature is the ability to drill through to a sensible selection of detail rows via double click on a measure (just like in the...
DAX
drillthrough
SSAS Tabular
CrassJoin's avatar
CrassJoin
Advocate I
7 years ago

Thanks Anonymous 

Can you link to an example of this? I tried USERELATIONSHIP with no joy.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Using RELATED(TABLE) is tricky with inactive relationships. Here's the code you're after.

 

New Table = 
SELECTCOLUMNS(
    'Fact',
    "Value For Column 1", RELATED( Dim[Value] ),
    "Value For Column 2",
        CALCULATE (
            CALCULATE (
                VALUES ( Dim[Value] ),
                'Fact'
            ),
            USERELATIONSHIP ( Dim[Column], 'Fact'[Column 2] ),
            ALL ( Dim )
        )
)

Assumptions:

  1. Fact is the fact table with 2 columns, Column 1 and Column 2. Both join on Column to Dim.
  2. Dim is a dimension table with Column and Value.
  3. The above retrieves the Value for Column 1 and Column 2 for each of of the rows in Fact.

If you want to know why so, please go to this article and read.

 

Best

Darek