topic DirectQuery with RLS- How to get Item level vs Total Level in Matrix in DAX Commands and Tips

DirectQuery with RLS- How to get Item level vs Total Level in Matrix

djurecic — Wed, 04 Mar 2026 19:17:17 GMT

Hello,

Normally, I would use ISINSCOPE or HASONEVALUE to accomplish this, but neither of them are supported for DirectQuery with RLS.

I'm trying to show the correct average value in the total row of a matrix without using either of these or AVERAGEX.

Thanks,

Re: DirectQuery with RLS- How to get Item level vs Total Level in Matrix

cengizhanarslan — Wed, 04 Mar 2026 18:56:22 GMT

I know you said "without using AVERAGEX", but I am not sure you could achieve without using those functions. Why can't you just use something like:

Avg Price = VAR Items = VALUES ( DimItem[Item] ) RETURN IF ( COUNTROWS(Items) = 1, [Avg Price], AVERAGEX ( Items, [Avg Price] ) )

Re: DirectQuery with RLS- How to get Item level vs Total Level in Matrix

djurecic — Wed, 04 Mar 2026 19:37:37 GMT

Thanks for your reply. I think I have misread the documentation. The exact wording is that

"This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules." So it can be used with RLS, just not in the RLS rules themselves.