Datasets - Execute Dax Queries

Executes Data Analysis Expressions (DAX) queries against the provided dataset. The dataset must reside in My workspace. The response is returned in the Apache Arrow format.

Permissions or query errors will result in:

• A response error, such as XMLA endpoint feature is disabled. Turn on the tenant setting 'Allow XMLA endpoints and Analyze in Excel with on-premises semantic models' to enable this feature..
• A successful HTTP status code (200) with a record batch containing the error details.

Permissions

The tenant setting Dataset Execute Queries REST API, found under Integration settings, must be enabled.

The user must have dataset read and build permissions. For more information, see Manage dataset access permissions.

Required Scope

Dataset.ReadWrite.All or Dataset.Read.All

Limitations

• This API is only available for datasets hosted on a capacity.
• Datasets that are hosted in Azure Analysis Services or that have a live connection to an on-premises Azure Analysis Services model aren't supported.
• One query per API call, but the query can have multiple evaluate statements.
• There's a limit of 120 query requests per minute per user, regardless of the dataset that's queried.
• To use Service Principals, make sure the admin tenant setting Allow service principals to use Power BI APIs under Developer settings is enabled. For more information on semantic models with RLS, refer to RLS limitations.
• Only DAX queries and INFO functions are supported at this time. MDX and DMV queries are not supported.

Response Format

The response body contains one or more concatenated Apache Arrow IPC streams. Each stream is self-contained with its own schema and record batches. To process the response, use an Apache Arrow client library.

The response may include the following rowset types, identified by schema-level metadata (key-value pairs on the Arrow schema):

• Data rowset: Contains query results. No special metadata keys. Column names and types are determined by the DAX query.
• Error rowset: Identified by IsError = true in schema metadata. Contains columns: ErrorCode, ErrorMessage, ErrorDescription, and source location fields. The schema metadata also includes FaultCode (hex error code) and FaultString (error message).

Record batches in the response use LZ4_FRAME compression. The pyarrow library handles this automatically. For .NET, install the Apache.Arrow.Compression NuGet package.

The following Python example shows how to read data and check for errors using pyarrow:

import io
import pyarrow as pa

# response = requests.post(url, headers=headers, json=request_body)
stream = io.BytesIO(response.content)
results = []

while stream.tell() < len(response.content):
    try:
        reader = pa.ipc.open_stream(stream)
        table = reader.read_all()
        metadata = {
            k.decode(): v.decode()
            for k, v in (reader.schema.metadata or {}).items()
        }
        if metadata.get("IsError") == "true":
            raise RuntimeError(
                f"Query error [{metadata.get('FaultCode')}]: "
                f"{metadata.get('FaultString')}"
            )
        else:
            results.append(table)
    except pa.ArrowInvalid:
        break

print(results[0].to_pandas())

For .NET, use the DaxQueryArrowResponseReader class in this SDK, which handles stream parsing, error detection, and LZ4 decompression.

POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeDaxQueries

URI Parameters

Request Body

Responses

Examples

Execute query with culture

POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeDaxQueries

{
  "query": "EVALUATE ROW(\"Formatted Date\", FORMAT(DATE(2024, 12, 31), \"Long Date\"))",
  "culture": "en-US"
}

Execute query with custom data

POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeDaxQueries

{
  "query": "EVALUATE FILTER('Sales', 'Sales'[Region] = CUSTOMDATA())",
  "customData": "North America"
}

Execute query with effective username

POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeDaxQueries

{
  "query": "EVALUATE SUMMARIZECOLUMNS('Sales'[Region], \"Total\", SUM('Sales'[Amount]))",
  "effectiveUsername": "user@contoso.com",
  "roles": [
    "SalesRole"
  ],
  "queryTimeout": 300
}

Execute simple DAX query

POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeDaxQueries

{
  "query": "EVALUATE VALUES('Product'[Category])",
  "queryTimeout": 600,
  "schemaOnly": false,
  "resultSetRowCountLimit": 100000
}

Definitions

DatasetExecuteDaxQueriesRequest

A request to execute queries against a dataset