UseRelationship() and Tabular Row Security

Reposted from Chris Webb's blog with the author's permission.

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.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .