We are very excited to announce the public preview of a new REST API to query datasets in Power BI by using Data Analysis Expressions (DAX). Among other things, this new DAX REST API helps to address customer feedback concerning programmatic access to the data in a dataset (for example, the idea REST API access to READ datasets with almost 500 votes by the time of this announcement). The new DAX REST API avoids dependencies on Analysis Services client libraries, requires no connection to XMLA endpoints, and works in Power BI Premium as well as in Power BI shared capacity. In short, there are many good reasons for BI solution developers to evaluate this REST API during the public preview period.
One of the greatest advantages of a REST API to query datasets is that you can use this REST API in practically any modern development environment on any platform, including low-code no-code Power Apps, Power Automate, and Logic Apps, JavaScript-based languages, PowerShell, Java, PHP, Ruby, Python, or any other technology that can authenticate with Azure Active Directory (AAD) and construct a Web request. Of course, you can also use .NET to call this REST API. As other Power BI REST APIs, the DAX REST API supports user accounts, service principals, and works in B2B scenarios, and because the caller is fully identifiable, row-level security (RLS) and other controls at the dataset level are applied as expected -- with the caveat that RLS is not supported for service principals. See the Limitations section in the Row-level security (RLS) with Power BI product documentation.
Yet, before you take this new REST API for a test spin, note that the tenant-level setting Allow XMLA endpoints and Analyze in Excel with on-premises datasets must be enabled in the Power BI admin portal, as depicted in the screenshot below. This setting is enabled by default, but double-check with your Power BI admin. At least for the duration of the public preview period, it seemed appropriate to govern this new DAX REST API with the existing XMLA endpoints tenant setting, The DAX query functionality is comparable to XMLA read access. We are still evaluating if this DAX REST API should have its own tenant setting. Please give us feedback if you want to influence this decision.
Equally important and also consistent with XMLA read access, you need the Build permission for the dataset you want to query by using this new REST API. Contributors and workspace admins have this permission, but you can also grant it directly, as in the following screenshot. For more information, see Build permission for shared datasets. Also note that the workspace must be a modern (v2) workspace. Classic (legacy) workspaces are not supported. So, if your datasets still reside in a classic workspace, make sure you upgrade the workspace to the new (v2) workspace experience.
Perhaps the easiest way to test the DAX REST API is to query a dataset by using the Power BI Management cmdlets. See the following screenshot for a straightforward sample script based on an AdventureWorks dataset.
Essentially, the above script performs the following main steps:
- Construct the request parameters. This includes the request URL and the request body.
- The request URL references the executeQueries endpoint of the dataset you want to query. The dataset Id identifies the dataset. There are many ways to determine the dataset id. Perhaps the easiest is to display the dataset settings in the browser and then copy the dataset Id from the URL in the address bar.
The URL has the format: https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries. If you use the Power BI Management cmdlets, you also can work with relative URLs, as in the script above. - The request body is a JSON document containing the DAX query you want to run and an optional parameter to specify if blanks should be returned as null values or not. The default omits blanks which helps to reduce the data volume. More importantly, note that the request body consists of a queries array, but currently you can only specify a single query. The queries array provides flexibility for future improvements, but currently it can only contain a single query item. Here’s a template for the request body. Replace ‘<Your DAX Query>’ with your actual DAX expression and don’t forget to escape quotation marks with a backslash (\”).
{ "queries": [{"query":"<Your DAX Query>"}], "serializerSettings":{"incudeNulls": true}}
- The request URL references the executeQueries endpoint of the dataset you want to query. The dataset Id identifies the dataset. There are many ways to determine the dataset id. Perhaps the easiest is to display the dataset settings in the browser and then copy the dataset Id from the URL in the address bar.
- Send the request to Power BI. The DAX REST API expects a POST request, which you can construct in PowerShell by using the Invoke-PowerBIRestMethod cmdlet. Don’t forget to login to Power BI and then specify Post as the Method and pass the request URL and request body as demonstrated above and the job is done. The Power BI Management cmdlets have the required app permissions to use the DAX REST API.
If you want to use your own app registration, make sure your app has the Dataset.Read.All or Dataset.ReadWrite.All permission. For details about registering an app with Power BI, see Register an Azure AD application to use with Power BI in the product documentation. - Parse the JSON response and process the results. The DAX REST API returns a JSON document with a results array containing one result per input query. Because the API currently only supports a single DAX query, the results array will also only include a single item. This item might include one or multiple tables depending on what the DAX query returns, which in turn contain the rows with the actual results as key/value pairs with the keys referring to the column names. In the script above, the line $parsed.results[0].tables[0].rows | Format-List outputs the rows of the first (and only) table of the first (and only) result item returned for the (one and only) DAX query.
As mentioned already, remember that the request body currently can only contain 1 DAX query and accordingly there is only 1 result set. Future versions of the API might support more. Also note that the result set is currently capped at 100k rows. If you need to retrieve more rows, you must construct and submit multiple DAX queries that each only retrieves an appropriate portion of the results. Moreover, the result set does not support binary data. Make sure your values are of the type string, numeric, boolean, blank, date time or variant. Needless to say that this is a Power BI API and therefore not available in Azure Analysis Services (Azure AS) or SQL Server Analysis Services (SSAS). And because this is a DAX REST API, you cannot submit MDX or DMV queries.
Current limitations in a nutshell:
- Only 1 DAX query can be executed per REST API request.
- Each DAX query can have only 1 result set.
- Result sets are capped at 100k rows.
- Supported data types: string, numeric, boolean, blank, datetime and variant. Binary is not supported.
- Azure AS and SSAS do not support this DAX REST API.