Forum Discussion
RLS over Direct Query
That sadly is not an option, not every report user has access to database.
But I have found a workaround:
I can bind parameter with the filtered value of imported table and modify Direct Query sources to filter to this single value using the parameter (in Power Query, or even in custom SQL), this way I get optimized SQL filtered to one value and RLS capability to make sure that only allowed information is displayed.
Hi FBergamaschi
sorry for the confusion.
Yes, dimension PARTY is imported, two direct query fact tables are connected to it and it works as intended:
Detailed page over DQ1 is using data from DQ1, and is filtered by PARTY.PARTY_ID (by DTD). Power BI sends select SQL statement over DQ1 with filter PARTY_ID = selectedparty. I guess it filters the imported table and using relationship it generates filtered SQL for DQ to fetch only datat that are required.
Use Case: show me all invocies of selected party
But when I add RLS to dimension PARTY (nothing else changes), it starts fetching all DQ data that are suitable for RLS, ignoring PARTY_ID filter. SQL in database is same, but without filter on PARTY_ID. It ends with timeout error (or too many rows error depends on the case) ... and my guess is that it tries to apply RLS first in SQL and apply standard filter later, while I need it to apply both in the SQL.
So I would expect PBI to "show me all invoices of selected party and show it to me only if I can see the party" but instead I think it does this "get all invoices of all parties the user can see and then filter it to selected party", which takes forever 😕
Is it easier to understand now?
Yes now it is clearer
So
1 - you apply RLS in Power BI Desktop
2 - you are getting errors (time out or out of memory) in Power BI Desktop / Cloud
Are 1 & 2 correct ?
If yes, do you get the error both in Dekstop and in Cloud or only in one of the two?
And just to understand, how many columns are rows are the table made of?
Best
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
FBergamaschi yeah, and the reson seems to be then before I apply RLS I can see SQL from Power BI to be filtered to one selected PARTY_ID, but after I apply RLS, the Power BI tries to load data unfiltered by PARTY_ID.
It is actually working fine on Desktop (using Show As functionality) but breaks after publishing to Cloud.Gazillion rows give or take 😄 thats why filtering it to one PARTY_ID is critical, I just need to make sure that user can see selected PARTY_ID.
OK so in this case can you implement RLS at the source database level? That should fix the thing. When th euser logs, PBI will pass the credentials to the DB
Let me know if this can work
Thanks
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
