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?  

[rjhale]

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.  

[lhammer1000]

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

@v-rzhou-msft is there anyway that this is not rolled out to our Power BI tenants?

[rjhale · Accepted 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. 

[lhammer1000]

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 ?

[lhammer1000]

@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. 😁

[rjhale]

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

[rjhale]

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?