RLS Lookupvalue is not allowed in directquery models

[chat_peters]

I have a simple data model (below) where I want to only display the incident IDs for those workers who have access to view the specialty ids attached to the incident ids. Worker table contains which specialty IDs are assigned to each worker. I have a RLS column that partially works.  Link to the sample power bi file 

I want to be able to retrieve all the rows that's applicable by specialty ID per worker instead of the MIN or the first non blank value

RLS Column: 

'Region'[Specialty ID] =

Calculate(FIRSTNONBLANK('Worker'[Specialty ID], [Specialty ID]

),

FILTER(

    ALL('Worker'),

    'Worker'[Worker ID]=1

   && 'Worker'[Specialty ID] IN Values ('Region'[Specialty ID])

)) 

Data model:

For example if I pass worker id 1 to the above measure instead of custom data I get this 

Desired results

[chat_peters]

@Anonymous  Hello! The region table does not have the field Worker[ID]. The relationship between region table and worker table can be established by connecting specialty ID columns. Here's a link to my file. I am going to try and leverage RELATED()

[chat_peters]

@Anonymous 

I did this and it worked out perfectly, Except it throws and error saying MAXX is not allowed in directquery 😞

[Specialty ID] =
CALCULATE (
    MAXX (
        FILTER (
            'Worker',
            'Worker'[Specialty ID]
                IN VALUES ( 'Region'[Specialty ID] )
                    && 'Worker'[Worker ID] = 2
        ),
        [Specialty ID]
    )
)

Please help! would it help if I imported the Region and Worker tables?

[Anonymous]

Hi @chat_peters ,

Maybe you can change your measure like this:

[Specialty ID] =
CALCULATETABLE (
    'Worker'[Specialty ID],
    FILTER (
        ALL ( 'Worker' ),
        'Worker'[Specialty ID]
            IN VALUES ( 'Region'[Specialty ID] )
                && 'Worker'[Worker ID] = 2
    )
)

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chat_peters]

@Anonymous Thank you for responding to my post. The relationship between Worker table and Region table is established through Specialty ID as Region table does not have the worker ID. One worker can have more than Specialty IDs assigned to them. I tweaked your dax expression to this but now it only returns the incidents attached the to the MAX of the specialty ID for a given worker.  

[Specialty ID] =
CALCULATE (
    MAX ( 'Worker'[Specialty ID] ),
    FILTER('Worker', 
    'Worker'[Specialty ID] in Values(
        'Region'[Specialty ID])&&
    'Worker'[Worker ID] = 1)
)

Desired result is this for worker ID = 1

[Anonymous]

Hi @chat_peters ,

Thank you for your question regarding adjusting RLS metrics to DirectQuery mode in Power BI. The following are the steps to adjust the RLS metric for DirectQuery mode:

1. Ensure that the data model has active relationships between tables.

2. You can create metrics that leverage existing relationships. For example, if there is a relationship between Worker[Worker ID] and Region[Worker ID], you can use the metric as shown below:

[Specialty ID] =
CALCULATE (
    MAX ( 'Worker'[Specialty ID] ),
    USERELATIONSHIP ( 'Worker'[Worker ID], 'Region'[Worker ID] ),
    'Worker'[Worker ID] = CUSTOMDATA ()
)

This metric assumes that there is a one-to-one relationship between the Worker ID and the Specialty ID. If this is not the case, you may need to adjust the metric accordingly.

3. Test it thoroughly to ensure that it applies RLS correctly in DirectQuery mode.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.