Forum Discussion
Datasets - Execute Query endpoint with RLS
Hello, this documentation is not clear about Rls identities. Can you send a body example?
I tested this and not filter the rls, return all rows.
{
"queries": [
{
"query": "EVALUATE VALUES(table)"
}
],
"identities": [
{
"username": "[email protected]",
"roles": [
"myrole"
],
"datasets": [
"xxx-xxx-xxx-xxxx"
]
}
]
}
Hi marcio_fornari,
Thank you for the follow-up and for sharing the request body you tested. You are correct in using the identities field in the Execute Queries endpoint to simulate RLS. However, RLS filtering only applies under specific conditions. Here are the steps to ensure it is configured correctly.
Requirements for RLS to Work:
- Premium or Fabric Capacity: The dataset must be in a workspace supported by Premium capacity (P SKU or Embedded A SKU) or Microsoft Fabric capacity.
- Defined RLS Roles: Ensure that the role name (such as "myrole" in your example) is precisely defined in the Power BI dataset under Manage roles in Power BI Desktop or service.
- Matching Identity: The username must align with what your DAX RLS expressions evaluate using USERNAME() or USERPRINCIPALNAME(). For instance, if your DAX rule filters based on email, the username must be the user's email.
Example Request Body (Correct Format):
{
"queries": [
{
"query": "EVALUATE VALUES('YourTableName')"
}
],
"identities": [
{
"username": "[email protected]",
"roles": [
"SalesRegionRole"
],
"datasets": [
"your-dataset-guid"
]
}
]
}
Required Headers: Ensure you are using an Azure AD bearer token with permissions to read from the dataset. The request must include:
Authorization: Bearer <your-access-token>
Content-Type: application/json
If you are still retrieving all rows, it typically indicates one of the following issues: The RLS role name does not match exactly. The dataset is not located in a Premium/Fabric workspace. Your dataset does not utilize USERNAME() or USERPRINCIPALNAME() in the DAX security rule. The token does not impersonate the user (though the identities field should simulate this if Premium capacity is enabled).
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
Hi marcio_fornari,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi marcio_fornari,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi marcio_fornari,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
Hi, sorry for a long time to answer.
not work for me 😞still return all data.
Hi marcio_fornari,
Apologize for the delayed reply. Thank you for your patience and the update. I have identified few workarounds that may help resolve the issue.
Premium/Fabric Capacity: Ensure the dataset is hosted in a workspace with Premium capacity (P or A SKU) or Microsoft Fabric capacity. Note that RLS simulation via the identities field is not supported in Pro-only workspaces.RLS Roles Are Properly Defined: Confirm that the role (e.g., "SalesRegionRole") is created in Power BI Desktop under Modelling > Manage Roles and published to the service.
DAX Expressions Use USERNAME() or USERPRINCIPALNAME(): Your DAX filters within the role should utilize these functions to evaluate the username from the API call:[EmailColumn] = USERPRINCIPALNAME()
Accurate Role Name and Username in API Payload: The roles field is case-sensitive and must match precisely. The username must correspond to what your RLS expression expects (typically an email).Azure AD Token: Ensure your request includes a valid Azure AD bearer token with permission to access the dataset. Sample JSON Payload:
{ "queries": [ { "query": "EVALUATE VALUES('YourTableName')" } ], "identities": [ { "username": "[email protected]", "roles": [ "YourRoleName" ], "datasets": [ "your-dataset-guid" ] } ] }Replace 'YourTableName' with the actual table name from your dataset
'YourRoleName' with your defined role
'your-dataset-guid' with the Dataset ID (GUID)
'[email protected]' with the value your RLS rule evaluates against
Ensure the role name matches exactly what’s defined in Power BI Desktop under Manage Roles.
Please refer these links for more information:
Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn
What is Power BI Premium? - Power BI | Microsoft Learn
Kindly refer to the below solved link for better understanding:
Solved: Rest API to query dataset with RLS - Microsoft Fabric CommunityIf this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.