DAX REST API - NullArray

[jhayes0128]

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:

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], AggregateException
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

Cannot index into a null array.
At J:\test.ps1:38 char:1
+ $parsed.results[0].tables[0].rows | Format-List
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : NullArray

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

I have tried these variations of the $requestUrl:

$requestUrl = "datasets/291666dc-09a2-44e3-ba8b-24b590fd9331/executeQueries"
$requestUrl = "https://api.powerbi.com/v1.0/myorg/datasets/291666dc-09a2-44e3-ba8b-24b590fd9331/executeQueries"
$requestUrl = "291666dc-09a2-44e3-ba8b-24b590fd9331/executeQueries"

And these variations of $requestBody:

EVALUATE SUMMARIZE('Calendar','Calendar'[Year],"Days in year",COUNT('Calendar'[Date]))
EVALUATE SUMMARIZE('Calendar','Calendar'[Year],\"Days in year\",COUNT('Calendar'[Date]))   add \ to "Days in year"
EVALUATE SUMMARIZE(Calendar,Calendar[Year],\"Days in year\",COUNT(Calendar[Date]))     remove ' from tablename and add \

I have verified that 'Allow XMLA endpoints' and API permissions are granted. Please help!

Microsoft Example: https://powerbi.microsoft.com/en-us/blog/announcing-the-public-preview-of-power-bi-rest-api-support-...

Related thread:https://community.powerbi.com/t5/Developer/New-API-Endpoint-with-DAX-Queries/m-p/1998477

[Anonymous · Accepted solution]

Hi @jhayes0128 

Refer to this code, you can try table expression which looks like {\"Calendar\"}.

Try this code in your reuqest body: 

EVALUATE SUMMARIZE({\"Calendar\"},'Calendar'[Year],\"Days in year\",COUNT('Calendar'[Date]))

Based the related post, the end user solved his problems by using datasets in V2 workspace. You need build permission to use rest api for Dax Queries.

For reference:

Please check what kind of workspace is your dataset in. Test datasets in your V2 workspace instead of My Workspace or V1.

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.