I'm attempting to apply row level security in a composite PowerBI model via DAX using a disconnected table containing the security information of all users.
Security table is called 'SecurityModel' and contains 2 columns, Userprincipalname and UserRegion.
On the tables in my model that have been imported (i.e. not direct query), the DAX I'm using below is working perfectly to secure the model ('Region' is a column in fact table that security is being applied to):
However, on a single direct query fact table with no joins to any other tables I'm getting the following error:
As I'm applying the filter to a single direct query table with no joins to any other tables I don't understand why I'm getting this error message.
The only thing I can see from other posts is that there was previously a preview feature to enable or disable cross filtering in direct query, see below.
However, this is not an available option in the December 2018 release and there are no other options I can see to turn this on and off for my single direct query table.
If anyone has any ideas or experience of this that they could share it would be really appreciated. Thanks.
In fact, I also not found any document mentioned this. Normally preview features will be remove when it update to stable version or rollback when it caused some issues.(compatibility or other issues)
You can download the last version of power bi desktop, if it works it means feature has been updated to stable version.
Appreciate the reply. I tried updating to the latest version today and still can't see this preview feature and am still getting the same error when applying the DAX to the single direct query table.
If this feature was previously selected in preview and you then update Power BI would this remain selected in the background by default? Even though the option to select this preview feature has disappeared.
If you had any more info on what happened to this preview feature that would be really helpful.
The misleading error message and a hidden product crash dump are a product defect that will be fixed in a future release. But the bug fix won't enable this modeling approach. For any DirectQuery table, be it in a composite model or in a pure DirectQuery model, RLS expressions only allow intra-row pure scalar functions. Measures and iteration functions like FILTER or COUNTROWS are not supported.
Thanks for the reply. Good to know it is a bug.
Are there any known DAX patterns for applying dynamic row level security on direct query tables that could be shared?
The only way I (sort of) got this to work was by connecting my single direct query table to an imported table containing my security data in and then applying the security on the imported table. However, this was so inefficient in terms of the SQL being passed back to the database via the connector that it was a non-starter.
With a complex many-to-many security model, trying to push this down into the underlying database tables is not really viable.
Any other advice help on how to get a dynamic security model working with one direct query table would be excellent. So close to utopia... :-)
If you can define RLS on a composite model, why can't you create all the necessary tables in the relational database so RLS rules and joins are pushed to SQL? Are you running SQL Server? Does Kapser's pattern https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/ apply to your scenario?