RLS with Direct Query (Data Source: Power BI Dataset)

[AdamHurleyEI]

Hello,

I'm facing a challenge with my Power BI model, which has multiple tables. One of these tables, my Fact Table, is a DirectQuery connected to a Power BI dataset. My goal is to implement Row-Level Security (RLS) using UserPrincipalName(), but I've encountered an issue. The fact table doesn't appear as an option when defining a new role in the RLS settings.

As a workaround, I created a calculated table using the SELECTCOLUMNS measure to select columns from my DirectQuery fact table. This workaround works perfectly in the Power BI Desktop environment. However, upon attempting to publish the report, I encountered an error message stating, "Refresh is not supported for datasets with a calculated table or calculated column that depends on a table which references Analysis Services using DirectQuery."

Could anyone provide guidance on how to resolve this issue? I would greatly appreciate any steps or instructions to overcome this challenge.

[v-binbinyu-msft · Accepted solution]

Hi @AdamHurleyEI ,

It sounds like you are trying to refresh a Power BI dataset that includes a calculated table that depends on another table that uses Direct Query mode to connect to an Analysis Services data source. Unfortunately, Power BI does not currently support refreshing datasets that include calculated tables that depend on tables using Direct Query mode.

One solution you could consider is to change the source data for the calculated table so that it does not use Direct Query mode. This would allow you to refresh the dataset, but you would need to find a way to incorporate the data from the Direct Query source into the calculated table using other means, such as using Power Query to merge or append the data.

Another option is to use a separate dataset for the calculated table, and then use a measure or a report-level filter to control the data displayed in the report based on the data in the calculated table. This would allow you to refresh the main dataset without affecting the calculated table.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.