Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

UseRelationship() and Tabular Row Security

with 5 comments

Quick summary: DAX measures in SSAS Tabular that use the UseRelationship() function return an error when row security is applied to a table. I’m surprised this hasn’t been documented somewhere – I know Marco came across it some time ago, but I ran into it again recently so I thought I’d mention it.

Consider the following simple SSAS Tabular model, based on Adventure Works DW:

image

There’s an active relationship between DateKey and OrderDateKey, and an inactive relationship between DateKey and ShipDateKey. The following measure returns the sum of Sales Amount and activates the inactive relationship:

Sales Amount by Ship Date:=
CALCULATE(SUM([SalesAmount]), USERELATIONSHIP(FactInternetSales[ShipDateKey], DimDate[DateKey]))

image

However, when there’s row-level security defined on the DimDate table (though not FactInternetSales) you will see an error for this measure when you browse the model:

image

image

ERROR – CALCULATION ABORTED: USERELATIONSHIP function cannot be used while querying table ‘FactInternetSales’ because of the row level security defined on table ‘DimDate’.

No workaround, I’m afraid, but this isn’t a bug, it’s a known limitation.

Written by Chris Webb

May 10, 2013 at 7:03 pm

Posted in DAX, Tabular

5 Responses

Subscribe to comments with RSS.

  1. L’ha ribloggato su Marchino BI Land.

    blogchino

    June 1, 2013 at 6:35 pm

  2. Hi Chris,
    do you have any alternative suggestion?

    Ahmet Aylin Öztürk

    July 2, 2014 at 9:45 am

    • No, I don’t have any general suggestions, sorry. There might be different design options based on your particular requirements though.

      Chris Webb

      July 2, 2014 at 10:14 pm

      • We are changing the model design. Thanks a lot… :)

        Ahmet Aylin Öztürk

        July 7, 2014 at 1:08 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,113 other followers

%d bloggers like this: