How to use Execute Queries DAX Power BI REST API?

This article explains you how to execute a DAX query using Power BI REST API.

As you might aware that we have a dedicated Power BI REST API to call the DAX queries against a specific Power BI semantic model.

API Details

This API is called  “Execute Queries”. This is available under the Datasets section.

Method: POST

Input Parameter – Workspace ID & Semantic Model (Dataset) ID.

Request Body – Need to pass the DAX query

Response – Table (Rows & Columns) in JSON format.

API – POST https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/executeQueries

Ref- Datasets – Execute Queries In Group – REST API (Power BI Power BI REST APIs) | Microsoft Learn

Use cases

1. We can consider this approach for Power BI data testing – We can compare the visual data against the database results.
2. We can build an alerting system with the help of Power Automate or other applications – Validate the row value against a threshold and send an alert.

We can easily try this API using the Try It option on the Power BI REST API document.

Everything is simple except the request body. You can get the DAX queries which you want to execute using any of the below approach,

1. Power BI Performance Analyser – Copy DAX query for your visuals
2. DAX Studio – Query Build – Build your DAX query
3. Manually write your DAX query

Example – Consider below query

DEFINE
    VAR __DS0FilterTable = TREATAS(
        {
            "Gold",
            "Green",
            "Orange",
            "Grey"
        },
        'DimProduct'[ColorName]
    )
    VAR __DS0Core = SUMMARIZECOLUMNS(
        'DimProduct'[ColorName],
        __DS0FilterTable,
        "SumSalesAmount", CALCULATE(SUM('FactSales'[SalesAmount]))
    )
    VAR __DS0PrimaryWindowed = TOPN(
        1001,
        __DS0Core,
        [SumSalesAmount],
        0,
        'DimProduct'[ColorName],
        1
    )
 
EVALUATE
    __DS0PrimaryWindowed
    ORDER BY
        [SumSalesAmount] DESC,
        'DimProduct'[ColorName] 

The above query can’t be executed directly using the API because the whole query should be covered with double quotes so we need to replace the double which are there inside query with the following character \”

Updated query:

DEFINE
    VAR __DS0FilterTable = TREATAS(
        {
            \"Gold\",
            \"Green\",
            \"Orange\",
            \"Grey\"
        },
        'DimProduct'[ColorName]
    )
    VAR __DS0Core = SUMMARIZECOLUMNS(
        'DimProduct'[ColorName],
        __DS0FilterTable,
        \"SumSalesAmount\", CALCULATE(SUM('FactSales'[SalesAmount]))
    )
    VAR __DS0PrimaryWindowed = TOPN(
        1001,
        __DS0Core,
        [SumSalesAmount],
        0,
        'DimProduct'[ColorName],
        1
    )
 
EVALUATE
    __DS0PrimaryWindowed
    ORDER BY
        [SumSalesAmount] DESC,
        'DimProduct'[ColorName]

The above query should be added on the request body.

{
  "queries": [
    {
      "query": "EVALUATE VALUES(MyTable)"
    }
  ]
}

Replace the above query with your updated query and you will see a response like below.

Happy learning!!. Share your feedback