Dynamic Row Level Security for Organizational Hierarchy in Direct Query Mode

[nleuck]

Hello All,

Does anyone know of a way to set up dynamic row level security in Direct Query Mode for a report that uses an organizational hierarchy? I've seen where you can do this in Import Mode through many difference resources but I need to able to implement it in Direct Query.

Thanks!

[v-xuding-msft]

Hi @nleuck ,

As I know, it is hard to do with DQ mode. The blog :Different approach to Dynamic Row Level Security  introduces a way that don't have to fight with table relationships. But we need to create a new security table manually. In this way, the connection mode of the report will be changed. And you need to check if the functions could be used while creating formulas. 

You could reference it to have a try. If it can't help you, I think there might be not a workaround to implement it.

Best Regards,

Xue Ding

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

[nleuck]

@v-xuding-msft 

Thanks for your response. Unfortunately, I need something similar to this:

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi 

I looked at the post you provided and LOOKUPVALUE doesn't work in Direct Query. So I created a new report in Import mode with my data and still couldn't it to work. Where he shows two values True and False in the table, I only show False. I don't think this is going to work for me.

[v-xuding-msft]

Hi @nleuck ,

I know the blog. I don't think we can implement it  with DQ mode. And after researching, I'm afraid there is not an idea to do it if the way I suggested can't work.  

You could submit your request to Power BI Ideas and vote it up to get the feature fixed quickly.

Best Regards,

Xue Ding

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

[nleuck]

@v-xuding-msft 

I think I can make it work by importing a user dataset with EmployeeID, Email, and ManagerID as my columns. I still can use what was in that article the only thing now is I go from Direct Query mode to a Mixed one. If I understand Mixed mode correctly my dataset that is Direct Query will still be a live connection and users can refresh the data without any issue in Power BI service. The new user dataset will be imported so I'll have to refresh it manually as needed, but this gives me the functionality that I need to make the dynamic row level security for the organizational hierarchy work.

[v-xuding-msft]

Hi @nleuck ,

Yes, you are right. The data with DQ mode will not be influenced. For the data with import mode, you could configure scheduled refresh. Then you don't have to refresh manually. It will do based on the scheduled time.

Best Regards,

Xue Ding

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

[nleuck]

@v-xuding-msft 

Correct me if I'm wrong, but the schedule refresh won't work unless the data in Power BI desktop has been refreshed first when using Import Mode. So the dataset that I imported wouldn't be affected until I select refresh in Power BI desktop. I don't see how a schedule refresh would help there unless I misunderstood that concept.

[v-xuding-msft]

Hi @nleuck ,

We don't have to refresh manually any more in Desktop if we configure scheduled refresh. The data will be up to latest automatically in Service.  

And if you want to view the latest data in Desktop as well, you could download the pbix file in Service after refresh completely.

Best Regards,

Xue Ding

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