topic Re: New API Endpoint with DAX Queries in Developer

New API Endpoint with DAX Queries

rjhale — Wed, 04 Aug 2021 19:20:43 GMT

MS recently announced support for DAX queries against published Power BI datasets through a new api endpoint: Announcing the public preview of Power BI REST API support for DAX Queries | Microsoft Power BI Blog | Microsoft Power BI

I've attempted the same setup using PowerShell cmdlets, but no matter which DAX query I provide it, I don't get any data back. The error I receive in PowerShell looks something like this:

+ $result = Invoke-PowerBIRestMethod -Method Post -URL $requestURL -Bod ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMe
thod], AggregateException
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

Cannot index into a null array.
+ $parsed.results[0].tables[0].rows | Format-List

Here is an example of one of the queries that I recently tried:

$requestURL = "datasets/8e66523b-b39b-42a5-a5b5-0d542365434/executeQueries" $requestBody = @" { "queries": [ { "query": "SUMMARIZE(Proposals, Proposals[DOCUMENT_ID], \"Total\", SUM(Proposals[DOCUMENT_TOTAL_AMT]))" } ] } "@ Login-PowerBI $result = Invoke-PowerBIRestMethod -Method Post -URL $requestURL -Body $requestBody $parsed = $result | ConvertFrom-Json $parsed.results[0].tables[0].rows | Format-List

Any thoughts on what I may be doing wrong here?

Re: New API Endpoint with DAX Queries

Anonymous — Mon, 09 Aug 2021 07:25:23 GMT

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:

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.

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.

Re: New API Endpoint with DAX Queries

rjhale — Mon, 09 Aug 2021 15:23:49 GMT

Thanks for the reply. I'm using the PowerShell cmdlets, and the documentation states "The Power BI Management cmdlets have the required app permissions to use the DAX REST API". I don't think I should need adjust any App permissions in Azure because of this.

We have verified that the Allow XMLA Endpoints and Analyze in Excel setting is enabled for the entire organization. Also, the account I'm using is the owner of the dataset, so it definitely has build permissions. I've also tried publishing the dataset to "My Workspace" and a v2 workspace. It doesn't seem to matter which workspace it's published to. I get the same error regardless.

Any other ideas or suggestions?

Re: New API Endpoint with DAX Queries

rjhale — Tue, 10 Aug 2021 15:19:22 GMT

Is there any chance this is only available to first/targeted release tenants or users? It didn't state it anywhere in the blog post, but I just tested the same dataset in our test tenant and I was able to connect from the Powershell cmdlets. I'm almost certain that our test tenant is first/targeted release.

Update: I'm not sure that would be the case either as my account has the targeted options enabed in both our test and production tenants.

Re: New API Endpoint with DAX Queries

Anonymous — Thu, 19 Aug 2021 06:41:40 GMT

I have exactly the same kind of issue as you @rjhale. Same error message.
I have also verified that the Rest API is enabled in the admin portal and that I have build access to the dataset.

$requestUrl = 'https://api.powerbi.com/v1.0/myorg/datasets/e500cd5d-4e48-4feb-80d5-33435a5c9a16/executeQueries'

$requestBody = @"
{ “queries”: [{“query”:”EVALUATE SUMMARIZECOLUMNS('Movements[countryCode]', \"Ship Count\", [shipCount])“}], “serializerSettings”:{“incudeNulls”: true}}
"@

Write-Host $requestBody

Write-Host 'Logging in'
Login-PowerBI
Write-Host 'Logged In'
$result = Invoke-PowerBIRestMethod -Method POST -Url $requestUrl -Body $requestBody
Resolve-PowerBIError -Last
Write-Host 'Invoked'
$result
$parsed = $result | ConvertFrom-Json
$parsed.results[0].tables[0].rows | Format-List
Write-Host 'Done'

I get the following response:

Logged In
Invoke-PowerBIRestMethod : One or more errors occurred.
At C:\Users\lchh\OneDrive - Bunker Holding Group\PowerShell\SampleDaxRestAPI.ps1:26 char:11
+ $result = Invoke-PowerBIRestMethod -Method POST -Url $requestUrl -Bod ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

Exception : System.Net.Http.HttpRequestException: Response status code does not indicate success: 400 (Bad Request).
at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__35.MoveNext()
InnerException : False
Message : Response status code does not indicate success: 400 (Bad Request).
StackTrace : at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__35.MoveNext()
HelpLink :
Source : System.Net.Http
ErrorDetails :
ErrorCategory : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at <ScriptBlock>, C:\Users\lchh\OneDrive - Bunker Holding Group\PowerShell\SampleDaxRestAPI.ps1: line 26

Invoked
Cannot index into a null array.
At C:\Users\lchh\OneDrive - Bunker Holding Group\PowerShell\SampleDaxRestAPI.ps1:31 char:1
+ $parsed.results[0].tables[0].rows | Format-List
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : NullArray

Done

@Anonymous is there anyway that this is not rolled out to our Power BI tenants?

Re: New API Endpoint with DAX Queries

rjhale — Thu, 19 Aug 2021 18:45:08 GMT

I finally got this partially worked out with the help of one of the very helpful Power BI engineers. For some reason datasets in My Workspace don't seem to be getting proper build permissions, so I decided to switch to just testing datasets in my v2 workspaces.

I initally thought that I had to use the format https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId} because that is what I'm accustomed to doing. This will probably change as this gets closer to GA, but for now you actually just remove the parts about the group and reference the dataset directly. For example: https://api.powerbi.com/v1.0/myorg/datasets/f388a618-2c93-41c5-8528-44c0c1129d70/executeQueries

Once I made that change, I was able to return data from a Powershell script and Postman. Still not sure about the permssions in My Workspace, but I'll likely never use that anway.

Re: New API Endpoint with DAX Queries

Anonymous — Fri, 20 Aug 2021 06:15:53 GMT

Thank you so much for your response @rjhale.
I'm really happy that you succeeded, because then I know there is a way.

I did go through the concerns that you describe.

1) My request url is directly to the dataset + '/executeQueries'

2) My workspace is a V.2 workspace

3) I am admin of the workspace, but just to make sure, I have given myself Build permissions directly on the dataset.

Can you please provide you code as sample code, so I can copy as much as possible from working powershell code, @rjhale ?

Re: New API Endpoint with DAX Queries

Anonymous — Fri, 20 Aug 2021 09:20:21 GMT

@rjhale I am quite embarrassed. There was a quirk in my code. I put a ' in the wrong place in the DAX code. It is working perfectly. Thanks for putting me on the right track. 😁

Re: New API Endpoint with DAX Queries

rjhale — Fri, 20 Aug 2021 12:29:58 GMT

@Anonymous Meh. Things happen. I'm really glad you were able to get it worked out.

Re: New API Endpoint with DAX Queries

jhayes0128 — Tue, 31 Aug 2021 17:53:22 GMT

@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!!

Re: New API Endpoint with DAX Queries

rjhale — Tue, 31 Aug 2021 19:22:42 GMT

One thing I noticed is that you didn't escape your double quotes in query. In the blog post they Kay mentions and don’t forget to escape quotation marks with a backslash (\”). The latest query that I copy and pasted from Power BI Desktop looked like this:

DEFINE VAR __DS0Core = SUMMARIZECOLUMNS( 'Proposals'[DOCUMENT_ID], \"SumDOCUMENT_DIRECT_AMT\", CALCULATE(SUM('Proposals'[DOCUMENT_DIRECT_AMT])) ) VAR __DS0PrimaryWindowed = TOPN(501, __DS0Core, 'Proposals'[DOCUMENT_ID], 1) EVALUATE __DS0PrimaryWindowed ORDER BY 'Proposals'[DOCUMENT_ID]

Hope that helps.

Re: New API Endpoint with DAX Queries

jhayes0128 — Tue, 31 Aug 2021 19:54:00 GMT

Same error 😥 Would you be able to post your $requestBody?

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

Re: New API Endpoint with DAX Queries

rjhale — Tue, 31 Aug 2021 20:16:57 GMT

The last query that I posted was actually from a Power Automate variable that I was working with. I also tested a different query using Powershell, and here is an example of the $requestBody variable:

$requestBody = @" { "queries": [ { "query": " DEFINE VAR __DS0FilterTable = TREATAS({\"CHEM\"}, 'Proposals'[DOCUMENT_DEPT_NAME]) VAR __DS0Core = SUMMARIZECOLUMNS( 'Proposals'[DOCUMENT_ID], __DS0FilterTable, \"SumDIRECT_AMT\", CALCULATE(SUM('Proposals'[DIRECT_AMT])) ) VAR __DS0PrimaryWindowed = TOPN(501, __DS0Core, 'Proposals'[ID], 1) EVALUATE __DS0PrimaryWindowed ORDER BY 'Proposals'[ID]" } ] } "@@

I would also make sure that your double quotes are the correct type of double quotes. I'm not sure if you copy and pasted right from your PS script, but it looks to me like you have two different types of double quotes being used.

Re: New API Endpoint with DAX Queries

Anonymous — Wed, 01 Sep 2021 06:13:16 GMT

And @jhayes0128. I also noted that in your code, you are not only using straight quotes (sorry for the probably not fully correct term). You should use " and not “ or ”.
Here is my simple request body:

# Here comes the DAX query wrapped in the request body. # Using @" "@ makes the text literal, which is much easier # Not that " in the DAX query must be preceeded by a \ # Port Calls Query - List ship count per country code $requestBody = @" { "queries": [ {"query": "EVALUATE SUMMARIZECOLUMNS( 'Movements'[countryCode], \"Ship Count\", [shipCount] )" } ], "serializerSettings":{"incudeNulls": true}} "@

Re: New API Endpoint with DAX Queries

jhayes0128 — Wed, 01 Sep 2021 13:21:50 GMT

You are my hero @Anonymous !!!! IT WORKS!

I never would've guessed this fix. thank you thank you thank you!!!

Re: New API Endpoint with DAX Queries

Anonymous — Wed, 15 Sep 2021 05:46:46 GMT

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

Re: New API Endpoint with DAX Queries

rjhale — Wed, 15 Sep 2021 11:43:50 GMT

I think you're running into the same problem that I was. Please look at my reply from 8/19. You can't use the URL with groups/groupid in it. For now, you have to reference the dataset without that information. For example: https://api.powerbi.com/v1.0/myorg/datasets/f388a618-2c93-41c5-8528-44c0c1129d70/executeQueries

Re: New API Endpoint with DAX Queries

Anonymous — Wed, 15 Sep 2021 17:41:17 GMT

I already tried that abs URL method and it doesnt work either.I get the same 400 (bad request) error.

Could you please provide all the steps you took to get this working end to end.It will be very helpful for many of us.

Re: New API Endpoint with DAX Queries

rjhale — Wed, 15 Sep 2021 18:08:26 GMT

For testing purposes, I was simply using PowerShell to authenticate and get a bearer token with my own credentials in the exact same way that Kay outlined in the Power BI blog post from last month. I would start troubleshooting by making sure you can use your own creds to return data from that dataset using the excecuteQueries endpoint.

You could also try using the "Resolve-PowerBIError -Last" command in PowerShell to see if you can get more information about where the invoke method is going wrong. Just a thought.

Re: New API Endpoint with DAX Queries

Anonymous — Wed, 15 Sep 2021 18:29:03 GMT

I use my cred initially and it didnt work.I also looked at the error but it is not very helpful.

I tried the same exact code few days ago with my creds and it hadnt worked,then i started the SP route.

This is what i get: