New API Endpoint with DAX Queries

[rjhale]

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?  

[Anonymous]

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 

[rjhale]

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

[Anonymous]

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.

[rjhale]

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.

[Anonymous]

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:

[rjhale]

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 used PowerShell to get the bearer token (using my own creds), and then added that to the headers like so:

• I then created a very simple Dax query that I knew would work against this dataset and added it to the Body:

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.  

[Anonymous]

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.

[rjhale]

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. 

[Anonymous]

Wanted to ask if you were able to use SPN and achieve the same thing?

[rjhale]

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.  

[Anonymous]

Thanks .I have also planned to use service account now after not being able to use SPN.

[Anonymous]

I am now receiving "401 unauthorized error" while using SPN.

Any thoughts on what might be missing?

[Anonymous]

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 

[jhayes0128]

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

[rjhale]

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.

[jhayes0128]

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}}
"@

[rjhale]

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. 

[lhammer1000]

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}}
"@

[jhayes0128]

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

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

[v-rzhou-msft]

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.