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

[djurecic]

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,

DJ

[djurecic]

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.

[cengizhanarslan · Accepted solution]

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