Forum Discussion

djurecic's avatar
djurecic
Super User
5 months ago
Solved

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

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

 

  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

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

     

2 Replies

  • djurecic's avatar
    djurecic
    Super User
    5 months ago

    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's avatar
    cengizhanarslan
    Super User
    5 months ago

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