New API Endpoint with DAX Queries

Hi rjhale 

Code format looks like as below.

{ “queries”: [{“query”:”<Your DAX Query>“}], “serializerSettings”:{“incudeNulls”: true}}

Please add the permissions for read and write in API permission in Azure Portal and see if the script then works.

Please check whether you have enable the permission in Admin portal in Power BI Service. 

Please check whether you have Build permission to this dataset and check whether this workspace is V2 instead of V1.

Current limitations in a nutshell:

1. Only 1 DAX query can be executed per REST API request.
2. Each DAX query can have only 1 result set.
3. Result sets are capped at 100k rows.
4. Supported data types: string, numeric, boolean, blank, datetime and variant. Binary is not supported.
5. Azure AS and SSAS do not support this DAX REST API.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rjhale Anonymous Could you please post your full working code? I am also having the same issues but having a hard time following the fixes in this thread.

My powershell code:

$requestUrl = "datasets/291666dc-09a2-44e3-ba8b-24b590fd9331/executeQueries"



$requestBody = @"
{ “queries”: [{“query”:”EVALUATE SUMMARIZE('Calendar','Calendar'[Year],"Days in year",COUNT('Calendar'[Date]))“}], “serializerSettings”:{“incudeNulls”: true}}
"@



Login-PowerBI
$result = Invoke-PowerBIRestMethod -Method Post -Url $requestUrl -Body $requestBody
$parsed = $result | ConvertFrom-Json
$parsed.results[0].tables[0].rows | Format-List

Error (same as yours):

Invoke-PowerBIRestMethod : One or more errors occurred.
At J:\test.ps1:35 char:11
+ $result = Invoke-PowerBIRestMethod -Method Post -Url $requestUrl -Bod ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateExce 
ption
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

The error suggests that the DAX is wrong, but it's copy/pasted from the source .pbix file.

I have tried removing the ' in the DAX. I have tried different urls. Same error every time. I'm losing it over here so any help you can provide is appreciated!!

Hi All,

Can someone pls point to what is missing in my approach.I spent lot of time trying to fix this but i coundt so far.

I tried a lot of things but i got a different error now and not sure of the reason.I was able to run dataset refresh and GET methods but unsuccessful with this "executing of DAX"

Error:

Error is constantly pointing to something wrong at the client end request and i am not sure what more to fix.

Here is my code:

I tried the same with refreshes endpoint as below and the service principal was able to refresh the dataset.

# refresh
$uri = "https://api.powerbi.com/v1.0/myorg/groups/$GroupId/datasets/$DatasetId/refreshes"
Invoke-RestMethod -Uri $uri –Headers $headers –Method POST –Verbose

Note: 

• I also used -Headers in the executequeries rest api but it didnt make a difference
• I registered an Azure app ,obtained the AppID,secretvalue,tenantID.I provided dataset.read and write permission for the app to call REST api.
• I created an AAD security group and added the service principal account into it as a member.
• Installed and imported all MicrosoftPowerBIMgmt modules in powershell
• Service principal is granted Admin on the dataset it is trying to query.
• Workspace is  V2