Execute DAX via. app registration and Power BI Rest API

[KimTutein]

Hi community

Has anyone ever tried querying Semantic model in Fabric using Rest API and an app registration (service principle) using the client id and a secret?

I am trying to use the “Datasets – Execute Queries” Rest API to execute DAX statements. I know the optimal way is semantic link library which works great but I have users not inside Fabric that are asking for access to run DAX (the long term plan is to get them inside Fabric but not an option in short term). I have tried the API out using Microsoft GUI “try it” (Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn) which works fine. However if I try to test this API in a Fabric notebook (I know – seems counterintuitive as we should use semantic link -  but it is just for a test) I cannot get it to work with an app registration.

I get this error when calling the API which offcource indicate some authentication problem – however I am not sure of what I am missing (my code example at the end in the post):

Error:

Response status code: 401 {'error': {'code': 'PowerBINotAuthorizedException', 'pbi.error': {'code': 'PowerBINotAuthorizedException', 'details': [], 'exceptionCulprit': 1, 'parameters': {}}}}

I have checked the following.

1. The workspace of the  semantic model is backed up by premium capacity (F128)
2. The app is admin in the workspace with the model
3. In the tenant setting both “allow service principals to call Fabric public APIs” and “Semantic Model Execute Queries REST API” is enabled for entire organization.
4. The service principle has been grandted Dataset.ReadWrite.All as of “delegated” type under API permission on the app registration. Admin consent has been granted.
5. I have tried getting a token from both 'https://analysis.windows.net/powerbi/api' and “https://analysis.windows.net/powerbi/api/.default. Same error message

My test code from a notebook

#Test DAX via. Rest API
#Datasets - Execute Queries - https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries

import requests
import invest_azure_fnk as iaf
af = iaf.azure_functions()
from pprint import pprint


# access token via. team data solutions app
access_token = af.get_access_token(resource =  'https://analysis.windows.net/powerbi/api') #custom library that can return an access_token for a given resource
#print(access_token)


url_test= F"https://api.powerbi.com/v1.0/myorg/datasets/74823857-dd72-41e9-8e01-7d548e28e61a/executeQueries"


# Headers 
headers = { "Content-Type": "application/json"
    , "Authorization": f"Bearer {access_token}" }

body = {
    "queries": [
        {
            "query": "EVALUATE VALUES('CALENDAR'[YEARNUMBER])"
        }
    ]
}


# Make the GET request 
response = requests.post(url_test, headers=headers, json = body) 
print('Response status code: ' + str(response.status_code))


try:
    pprint(response.json())
except:
    print('cannot print response as json')

[KimTutein]

Hi @v-kpoloju-msft 

Thank you for your detailed explanation – it is a huge help for me.

Regarding adding the application permission it seems like it is not possible from the GUI in Azure portal – it simply does not show up. I tried some chatbots and they suggest one have to manually update the manifest of the app registration. Do you know if this is really correct and if so do you have an input to how I would do this?

Appendix

The only permission that shows up is tenant.read.all og tenant.readwrite.all

[v-kpoloju-msft]

Hi @KimTutein,

Thanks for raising your question. Based on your detailed explanation and the error message (PowerBINotAuthorizedException – 401), it appears that the issue stems from authentication using a service principal (app registration) with delegated permissions, which is not supported for the Execute Queries API when used outside a user context.

The ExecuteQueries REST API for Power BI requires proper authentication using a service principal with application permissions.

In your case, you're using delegated permissions (Dataset.ReadWrite.All), which only work in the context of a signed-in user. However, a service principal (client ID + secret) represents an application identity without a user context, so this approach leads to a 401 Unauthorized error.

Go to your app registration in Azure and: Remove the Delegated permission (Dataset.ReadWrite.All). Add the Application permission instead. Choose Dataset.Read.All or Dataset.ReadWrite.All under Application permissions. Don’t forget to click “Grant admin consent” after adding it. Configure a service principal to call Power BI APIs

Since you are using a service principal, you need to get the token using the client credentials flow (not a user-based flow). That means grant_type should be client_credentials. scope should be: https://analysis.windows.net/powerbi/api/.default

OAuth 2.0 client credentials flow

Double-check tenant settings: It is great that you have already enabled the right tenant settings! Just make sure: Allow service principals to use Power BI APIs is turned on. Semantic model Execute Queries REST API is enabled for the whole org.

Make sure the app is added to the workspace: Even if the app is set up correctly in Azure, it also needs to be added as a member or Admin in the Premium workspace where your semantic model lives. Workspace roles in Power BI

Let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[KimTutein]

Hi @v-kpoloju-msft 

Just to let you know we tried

1. adding Tenant.ReadWrite.All as “application” (as the Dataset.ReadWrite.All” was not a permission we could chose for application level).
2. We are using the Oauth 2.0 client crededent flow via “https://analysis.windows.net/powerbi/api/.default”
3. Double check the service principle we use is admin on the workspace with the smenatic model

The error is still:

Response status code: 401 {'error': {'code': 'PowerBINotAuthorizedException', 'pbi.error': {'code': 'PowerBINotAuthorizedException', 'details': [], 'exceptionCulprit': 1, 'parameters': {}}}}

Tenant settings:

Granted api permissions

[v-kpoloju-msft]

Hi @KimTutein,

Thank you for the detailed follow-up and confirming the steps you have already taken that helps a lot.

Since you are still encountering the PowerBINotAuthorizedException despite correct tenant settings and admin rights, I recommend verifying a few additional things that often cause this issue:

• Tenant Setting API Access for Service Principals: Ensure that the "Allow service principals to use Power BI APIs" setting is enabled in the Power BI Admin portal. If it is scoped to security groups, please confirm that your service principal is part of an allowed group.
• Build Permission on Dataset: Even if the service principal is a Workspace Admin, please verify it has Build permissions on the specific dataset, especially when using the ExecuteQueries API.
• Check Dataset RLS or EffectiveIdentity: If your dataset uses Row-Level Security, ensure the SP is added to the role or that the EffectiveIdentity is used correctly when making the query.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[KimTutein]

Hi @v-kpoloju-msft 

I now tried out a new app registration and sadly with the same result.

I have checked settings again and the only thing I cannot do is explicitly add build permission on dataset. It seems this cannot be done on an app registration – however the app registration is admin on the workspace so it should have all the permission it needs? However I tried explicit adding the security group with the app in it and giving it Build (and everyting else) - it says "all permissins granted - that did not help either.

So to sum up:

1. Tennant settings
   1. “service principal can call Fabric public API’s” => enabled for entire organization
   2. “Semantic model execute queries REST API” => enabled for entire organization
   3. (“service principal can access read-only admin APIs”) => enabled for subset of the organization – this app is in the the security group
2. API permission on the app
   1. Tenant.ReadWrite.All as “application” (with admin consent)
3. Workspace permission
   1. App is admin (I cannot explicit set build permission but I would believe this should be included in workspace admin?). Explicit adding security group with the app in it with build permission did not help either.
4. Model RLS
   1. We do not use RLS in any of our semantic models
5. Access token
   1. I use this scope: "https://analysis.windows.net/powerbi/api/.default"  
   2. I request token via. OAuth 2.0 via. : "https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"

[v-kpoloju-msft]

Hi @KimTutein,

Thank you for testing with a new app registration and for sharing such a detailed summary that really helps narrow things down.

From what you have described, it sounds like most of the core tenant settings and permissions are correctly in place. However, one key detail stands out while your app registration is a Workspace Admin, it seems it does not have explicit Build permissions on the dataset.

This is a common point of confusion in the Power BI service, Service Principals (SPs) typically still require Build permission at the dataset level, even if they’re workspace admins. Unlike user accounts, this permission often does not come automatically and unfortunately, it can’t be added via the UI. It usually needs to be set programmatically.

This could explain why the API calls are failing the SP likely does not have the dataset-level access it needs to execute queries or perform related operations.

You might also consider reviewing the effective permissions the SP has on the dataset, just to confirm whether Build is being applied or not. That would help verify if this is indeed the missing piece.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[KimTutein]

Hi @v-kpoloju-msft 

Hi

Thank you for your feedback.

Regarding permission it is very strange to me that Service Principle access does not follow the normal permission model in the UI (that is for instance an admin on workspace has build access on the semantic model) but I get that could explain it then. You say this must be added programmatically – could this be a REST API? I tried looking into Power BI Rest API Dataset – Put Dataset User in Group (Datasets - Put Dataset User In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn). However this API explicit says that “Updating permissions to service principals (app principalType) isn't supported”.

Can you elaborate on how you would set the build permission programmatically?

[v-kpoloju-msft]

Hi @KimTutein,

Thanks for the thoughtful follow-up and yes, you are right to question the behaviour. It does feel a bit counterintuitive that Service Principals, even as Workspace Admins, do not automatically get Build permissions on semantic models (datasets). But in practice, they do need that permission to be explicitly granted, and this has been a common stumbling block for many.

You're also right that the older Put Dataset User in Group API does not support App (Service Principal) as a principal type and that is why you are seeing that limitation noted in the documentation. Admin - Groups AddUserAsAdmin - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Admin - REST API (Power BI Power BI REST APIs) | Microsoft Learn

This API does support principalType = App, and it allows you to grant Build access to a dataset on behalf of a Service Principal, even though it can't be done from the UI.

You would:

• Call this endpoint as a Power BI Service Admin.
• Use App as the principalType.
• Set datasetAccessRight to "Build".

This is the supported approach for assigning dataset-level access to Service Principals.
Power BI REST APIs semantic model permissions - Power BI | Microsoft Learn

Using AddUserAsAdmin with principalType: App is the right and currently supported method for enabling this scenario.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[KimTutein]

Hi @v-kpoloju-msft 

I can use this api: Admin - Groups AddUserAsAdmin - REST API (Power BI Power BI REST APIs) | Microsoft Learn to add the service principle to the workspace as admin (using the object id of the enterprise application (I allso see this reflected in the GUI).

The body is like this:

{

                             "groupUserAccessRight": "Admin",

                             "identifier": "[id of enterprise application]",

                             "principalType": "App"

}

If I understand you correct I then need to call another api to grant the service principle with “datasetAccessRight” to build. However you did not specify which api I should use for this (and the learn article reference reference the api that cannot be used with service principle)?

[v-kpoloju-msft · Accepted solution]

Hi @KimTutein,

You're right in your understanding after assigning the Service Principal as an Admin on the workspace using the AddUserAsAdmin API, there is still one more step needed. You will also need to grant Build permissions on the specific dataset so that the Service Principal can access or create reports from it.

The correct API for this is the one that lets you assign dataset-level permissions specifically the Build permission. This is different from just being an Admin on the workspace, as dataset access needs to be granted separately.

You can find more details about this in the official Microsoft Learn page on semantic model (dataset) permissions here: Power BI REST APIs semantic model permissions – Microsoft Learn

If you are having trouble opening the link, I suggest trying a different browser or using incognito mode. Sometimes corporate networks or browser cache may cause issues with Learn pages.

Also, the general Power BI REST API overview page below can help if you're working on automation or embedding scenarios: Power BI REST APIs for embedded analytics and automation – Microsoft Learn

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[KimTutein]

Hi all

After doing lots and lots of testing I found out that my semantic models that uses direct lake cased the error when the cloud connections was sat to “default Single Sign on (Entra ID)” – after setting the model to use an explicit connection to the underlying data lake the last error disappeared and I was able to execure DAX using a rest API.

Thanks you @v-kpoloju-msft  for providing all the feedback which allso was a part of the problem.

[KimTutein]

Hi @v-kpoloju-msft 

I am in the process of getting a new app registration which "only" has the read/write tennant setting on app level. When I tried this on my "old" app we were no longer able to use the app for getting data on admin API's which we then needed to fix. I awaite new app and will test and let you know.

Thank you for all the feedback you have allready given to me.