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.