DAX standalone query execution using python

[safrasmusthafa]

Hi, iwanted to execute DAX query through service principal in python standalone (vsCOde). but i got the error as below.

Status Code: 401
{"error":{"code":"PowerBINotAuthorizedException","pbi.error":{"code":"PowerBINotAuthorizedException","parameters":{},"details":[],"exceptionCulprit":1}}}

[v-karpurapud]

Hi @safrasmusthafa 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @safrasmusthafa 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud]

Hi @safrasmusthafa 

Thank you for sharing the updated configuration and Python code. Even though the service principal has tenant‑level access, is added to the workspace, and the workspace is running on Fabric (F2) capacity, the 401 PowerBINotAuthorizedException occurs because the REST API executeQueries endpoint does not support DAX execution when using a service principal. In Premium/Fabric workspaces, service principals can run DAX only through the workspace’s XMLA endpoint (powerbi://...), as XMLA is the only interface that accepts application‑only authentication for DAX operations. To execute DAX from Python, you will need to use an XMLA‑compatible client library (such as pyadomd) and authenticate using the client‑credentials OAuth flow with your tenant ID, client ID, and client secret. Once connected through the XMLA endpoint, the service principal will have the necessary authorization to run standalone DAX queries successfully.

Regards,

Microsoft Fabric Community Support Team.
 

[safrasmusthafa]

hi @v-karpurapud thank you for your reply. i have verified that all necessary access is enabled on tenant level and also service principal is added to workspace and fabric capacity is F2. below is the query i used.

import requests

# ========== CONFIG ==========
TENANT_ID = "************************************"
CLIENT_ID = "************************************"
CLIENT_SECRET = "************************************"
DATASET_ID = "************************************"
WORKSPACE_ID = "************************************"

# Example DAX query (keep it simple for validation)
DAX_QUERY = """
EVALUATE ROW("Ping", 1)

"""

# ========== STEP 1: GET ACCESS TOKEN ==========
token_url = f"https://login.microsoftonline.com/{TENANT_ID}/oauth2/v2.0/token"

token_payload = {
"grant_type": "client_credentials",
"client_id": CLIENT_ID,
"client_secret": CLIENT_SECRET,
"scope": "https://analysis.windows.net/powerbi/api/.default"
}

token_response = requests.post(token_url, data=token_payload)
token_response.raise_for_status()
access_token = token_response.json()["access_token"]

print("✅ Access token acquired")
# print(access_token)

# ========== STEP 2: EXECUTE DAX QUERY ==========
execute_url = (
f"https://api.powerbi.com/v1.0/myorg/datasets/"
f"{DATASET_ID}/executeQueries"
)

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

query_payload = {
"queries": [
{
"query": DAX_QUERY
}
],
"serializerSettings": {
"includeNulls": True
}
}

response = requests.post(execute_url, headers=headers, json=query_payload)

# ========== STEP 3: HANDLE RESPONSE ==========
if response.status_code == 200:
print("✅ DAX query executed successfully")
print(response.json())
else:
print("❌ DAX query failed")
print("Status Code:", response.status_code)
print(response.text)


# Test 1: Can list workspaces?
r1 = requests.get(
"https://api.powerbi.com/v1.0/myorg/groups",
headers=headers
)
print("Groups:", r1.status_code)
print("Groups_text:", r1.text)

r2 = requests.get(
f"https://api.powerbi.com/v1.0/myorg/groups/{WORKSPACE_ID}/datasets",
headers=headers
)

print("Datasets:", r2.status_code)
print(r2.text)

[v-karpurapud]

Hi @safrasmusthafa 

Thank you for reaching out to the Microsoft Fabric community forum.

The PowerBINotAuthorizedException (401) happens when a service principal does not have permission to run DAX queries on a Power BI dataset. To allow DAX execution, make sure the workspace is on a Premium or Fabric capacity, since shared capacity does not support XMLA endpoint operations. The tenant setting “Allow service principals to use Power BI APIs” must be turned on, and the service principal should be added to the workspace, ideally as a Workspace Admin for full XMLA read/write access. When running queries from Python, use the client-credentials OAuth flow with the correct tenant ID, client ID, and client secret. Connect using the workspace’s XMLA endpoint (powerbi://…), as DAX queries cannot be run through the REST dataset endpoint. With these steps, the service principal will be authorized to execute DAX queries.

If you have any more questions, please let us know and we’ll be happy to help.

Regards,

Microsoft Fabric Community Support Team.