Dynamic Row Level Security DirectQuery

[gbiniek]

I have successfully implemented RLS in DirectQuery mode utilizing the USERNAME() function however do to the size of the dataset the time it takes to render the report takes too long and makes the report useless.  The main culprit for the time it takes to render is a Item Dimension (millions of items).  These items are flagged by customer.

The Role defined for RLS within PowerBI, if I setup a Table Filter over that dimension as such the report is extremely fast.

[CustomerAlternateKey] IN { "ABC", "XYZ" }

Any thoughts on how to build the rule above to dynamically determine the keys in DirectQuery mode?

Thanks!!!

[Anonymous]

@gbiniek,

In DirectQuery mode, your report performance depends largely on the performance of the underlying data source. ,Moreover, in this case,enabling row-level security can result in substantial changes in performance.

In your scenario, please check the execution plans for your queries when you define different row level security roles, add indices for commonly filtered columns. In addition, check the best practices for connecting via DirectQuery in this official article.

Regards,
Lydia