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:


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]))


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:



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.


    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: Logo

You are commenting using your 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


Get every new post delivered to your Inbox.

Join 4,006 other followers

%d bloggers like this: