DAX · Tabular

UseRelationship() and Tabular Row Security

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.

9 thoughts on “UseRelationship() and Tabular Row Security

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

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

  1. What a worthless blog this one. I thought to find a solution, workaround here but its just stating something i allready know since I’m not googling this error just for fun. Damn clickbait.

    1. I disagree. If Chris Webb doesn’t have a solution, and has confirmed it is a known limitation. Then the rest of us can make a call to stop looking for a solution and try another method.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.