topic Re: SSAS Tabular - Default Detail Rows Expression in DAX Commands and Tips

SSAS Tabular - Default Detail Rows Expression

CrassJoin — Wed, 14 Aug 2019 11:50:08 GMT

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 old school Excel pivot tables that your finance department can't part with). The content of the drillthough is determined by the table property [Default Detail Rows Expression]. The example Microsoft provides here is great insofar as it goes.

SELECTCOLUMNS(
    'Internet Sales',
    "Customer First Name", RELATED( Customer[Last Name]),
    "Customer Last Name", RELATED( Customer[First Name]),
    "Order Date", 'Internet Sales'[Order Date],
    "Internet Total Sales", [Internet Total Sales]
)

My problem is that I have a calendar table with an active relationship with my fact table (let's call this fact column end_date_sk) and an inactive relationship with the same table (let's call this fact column start_date_sk). If I want to return calendar columns from both the inactive and active relationships, what do I do? RELATED() only seems to work for the active relationship.

Re: SSAS Tabular - Default Detail Rows Expression

Anonymous — Wed, 14 Aug 2019 14:09:28 GMT

You have to change the relationship via a combination of CALCULATE and USERELATIONSHIP. But be careful where you do it. RELATED must work AFTER the relationship has been activated, not before.

Best
Darek

Re: SSAS Tabular - Default Detail Rows Expression

CrassJoin — Wed, 14 Aug 2019 21:48:44 GMT

Thanks Anonymous

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

Re: SSAS Tabular - Default Detail Rows Expression

Anonymous — Thu, 15 Aug 2019 09:09:57 GMT

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:

Fact is the fact table with 2 columns, Column 1 and Column 2. Both join on Column to Dim.
Dim is a dimension table with Column and Value.
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