Benefits of using DAX INFO() functions instead of the DMVs via the XMLA endpoint in Power BI

The XMLA endpoint is the standard way to query a model in Power BI or Fabric for data and metadata. One major limitation though is that it is only available for workspaces in Premium or Fabric capacities. Every query is charged to your capacity resulting in a CU cost (it shows as interactive consumption in the Capacity metrics).

An interesting alternative to the XMLA endpoint is the Execute Queries API call. This method allows queries to models residing in shared / Pro Workspaces as well.

Additionally, I am not aware of any cost incurred (in terms of CUs in case of a paid capacity). Since the DAX INFO() functions are getting more and more complete (INFO.CALCDEPENDENCY was just added in the June update of Power BI), this has really offered an alternative for querying semantic models.

However, there are also some constraints: each query is limited to 100,000 rows or 1,000,000 values, and there is a limit of 120 queries per minute per user. In most cases, the limitations will not be an issue.

Using DAX INFO() functions we can get a lot of the DMVs that we are used to getting via the XMLA endpoint.

For example INFO.CALCDEPENDENCY is equivalent to DISCOVER_CALC_DEPENDENCY

You can see a list INFO functions and the respective DMV queries here.

This post will demonstrate an easy way to execute a DAX query against a model in any workspace (if the user has access) using a simple Python script and exporting the results to an Excel file. 

You can basically execute any DAX query using this API call.

Requirements 

Three requirements must be met to execute this API call: 

1. The tenant setting "Dataset Execute Queries REST API" must be enabled (Admin portal > Tenant Settings > Integration settings) see below. 

2. The user must have access to the workspace where the semantic model resides. 

3. You need to have the dataset and group id of the model and workspace (group)

 

How to (Python 3.12): 

The URI Parameters for the Execute Queries In Group call are the Group ID (Workspace ID) and the Dataset ID (Semantic model ID). You can find these by opening the model in the Power BI service and checking your URL. 

In this example, we use Python version 3.12.4 along with the following packages: Pandas version 2.2.2, requests version 2.32.3, and azure-identity version 1.17.1 for authentication.

After installing the packages, run the Python script below, replacing the variables workspace_id, semantic_model_id, dax_query, and excel_path with your values: 

Please replace WORKSPACE ID and MODELID accordingly

from azure.identity import InteractiveBrowserCredential 
import requests 
import pandas as pd 

# Replace the following variables with your own values 
workspace_id = "WORKSPACEID" 
semantic_model_id ="MODELID" 
dax_query = "EVALUATE INFO.CALCDEPENDENCY()" 

excel_path = "daxQuery.xlsx" 

# Do not change the following code: 
api = "https://analysis.windows.net/powerbi/api/.default" 
authority = "https://login.microsoftonline.com/" 
auth = InteractiveBrowserCredential(authority=authority) 
access_token = auth.get_token(api).token 
url=f"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/datasets/{semantic_model_id}/executeQueries" 
payload = {"queries": [{"query": dax_query}]} 
header = {"Authorization": f"Bearer {access_token}"} 

response = requests.post(url, headers=header, json=payload) 

if response.status_code == 200: # Case its a success 

    data = response.json() 
    rows = data['results'][0]['tables'][0]['rows'] # Extract the rows data 
    df = pd.DataFrame(rows) # Create the DataFrame 
    df.to_excel(excel_path, index=False) 

 

The example script will execute the EVALUATE dimCountries query and save the results as an Excel file. Step by step, it will first open a browser for authentication using the InteractiveBrowserCredential class. After that, it will get the token and pass it to the header. Then it will execute the POST request, get the JSON data, parse the rows field of the response, and create a Pandas DataFrame. 

Conclusion 

Using the Execute Queries In Group API allows users to overcome some XMLA limitations, such as being restricted to premium workspaces. It can also reduce capacity usage, depending on your previous use of XMLA connections in your tenant. In general, it is a great and easy way to query small to medium size semantic models within your Power BI tenant.