Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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...
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?
Solved! Go to Solution.
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.
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 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
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.
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.
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:
At this point I would suggest testing this in Postman. Hopefully, you're familiar enough with Postman to setup a test request. I setup a very basic test on my end like this:
I would think that would give you more information if it continues to fail. Make sure your account has Build permissions to the dataset you're trying to query.
Thanks a lot for your help.
I tried in postman but i constantly got the invalid hostname error.
So i switched back to tryig in PS and i wrote a simple DAx same as you and logged in as 'me' and it had worked.
Glad to hear you got that far. Sounds like it may be the result of you Dax syntax. I started using the performance analyzer in Power BI desktop to create my Dax queries.. It's a really quick way of getting the right syntax for the various tables, fields, and aggregations. Just thought I would share that tip.
Wanted to ask if you were able to use SPN and achieve the same thing?
I haven't tried. Getting an SPN setup in our environment is complicated because of organizational beauracracy. Besides that, we're really interested in using it within Power Automate to pull data from a published PBI dataset and loop over it. In Power Automate, you can use an Invoke an HTTP Request to issue API requests like this:
The creds are stored like all the other connections, and we're able to use a service account with build rights on those datasets.
Thanks .I have also planned to use service account now after not being able to use SPN.
I am now receiving "401 unauthorized error" while using SPN.
Any thoughts on what might be missing?
Oh I usually take the query from perf analyzer.Can't point out the actual problem but I feel something in the URL body was not correct and accepted
@rjhale @lhammer1000 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!!
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.
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}}
"@
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.
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}}
"@
You are my hero @lhammer1000 !!!! IT WORKS!
I never would've guessed this fix. thank you thank you thank you!!!
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:
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
4 | |
3 | |
2 | |
2 | |
1 |
User | Count |
---|---|
10 | |
7 | |
3 | |
2 | |
2 |