'LOOKUPVALUE' is not allowed as part of the row level security expression on DirectQuery models

[jc508]

In Power-BI we are connecting to SqlServer (NOT SSAS and NOT a tabular model)
and get the error 'LOOKUPVALUE' is not allowed as part of the row level security expression on DirectQuery models
We are trying to convert some reports to run without SSAS now that row level security is available in Power-Bi
The expression was lifted from the SSAS version we are migrating from.

https://docs.microsoft.com/en-us/sql/analysis-services/supplemental-lesson-implement-dynamic-securit...
updated 8th May still says this is the method to use.
If this function is not allowed then what functions are allowed ?

Syntax used is
'Fact Purchase Row Status'[DDS_COST_CODE_SK]=LOOKUPVALUE('Dimension User Cost Code Access'[DDS_COST_CODE_SK]
    , 'Dimension User Cost Code Access'[USERNAME_TXT], mid(username(), search("\", username()) + 1, 20)
    , 'Dimension User Cost Code Access'[DDS_COST_CODE_SK] ,'Fact Purchase Row Status'[DDS_COST_CODE_SK] )

[v-shex-msft]

Hi @jc508,

You can try to use calculate function with firstnonblank and filters to achieve lookup operation.

'Fact Purchase Row Status'[DDS_COST_CODE_SK] =
CALCULATE (
    FIRSTNONBLANK (
        'Dimension User Cost Code Access'[DDS_COST_CODE_SK],
        [DDS_COST_CODE_SK]
    ),
    FILTER (
        ALL ( 'Dimension User Cost Code Access' ),
        'Dimension User Cost Code Access'[USERNAME_TXT]
            = MID ( USERNAME (), SEARCH ( "\", USERNAME () ) + 1, 20 )
            && 'Dimension User Cost Code Access'[DDS_COST_CODE_SK]
                = SELECTEDVALUE ( 'Fact Purchase Row Status'[DDS_COST_CODE_SK] )
    )
)

In addition, you can also turn on below option to write unrestricted measures in directquery mode.

Regards,

Xiaoxin Sheng 

[jc508]

Thanks v-shex-msft

I tried that formula as is and it gets an error

'Function CALCULATE is not allowed as part of the row level security expression on DirectQuery models.'

Looks like there are no functions that you can use with row level security !!

Also ticking that option seems to make no difference - maybe only applies to measures not RLS filters ???

I will fiddle with it some more but thanks anyway

JC

[v-shex-msft]

Hi @jc508,

I guest you're try to create a calculated column in direct query mode, right? If this is a case, current most of functions not allowed to use in calculated column when you use direct query mode.

I'd like to suggest you use measure to instead.

Regards,

Xiaoxin Sheng

[rajaniesh]

is this fixed by Microsoft or it is still the same issue?Is there any alternate method to do it?