UseRelationship() and Tabular Row Security

DAX, Tabular 1 Minute

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

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.

Published by 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.

Published

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.

      Reply
  3. Pingback: SSAS Tabular Mode’da USERELATIONSHIP Fonksiyonunda Önemli Bir Kısıt | Mshowto

  4. 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.

    Reply

    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.

      Reply

Leave a ReplyCancel reply