PowerBI API - Execute Queries on Dataset - Problem with DAX Statement

[RubenSchilling]

Good morning,

i am not able to execute any queries via the Execute query REST API, except when i ONLY use evaluate.

As you can see this one works without problem:

{
"queries": [
{
"query": "EVALUATE VALUES(Manufacturer)"
}
],
"serializerSettings": {
"includeNulls": true
}
}

RESULT:

{
"results": [
{
"tables": [
{
"rows": [
{
"Manufacturer[ManufacturerID]": 6,
"Manufacturer[Manufacturer]": "Leo",
"Manufacturer[MfgisVanArsdel]": "No"

But when i literally use any other query like this:

{
"queries": [
{
"query": "AVERAGE(Sentiment[Score])"
}
],
"serializerSettings": {
"includeNulls": true
}
}

The Result usually shows something like:

{
"error": {
"code": "DatasetExecuteQueriesError",
"pbi.error": {
"code": "DatasetExecuteQueriesError",
"parameters": {},
"details": [
{
"code": "DetailsMessage",
"detail": {
"type": 1,
"value": "Query (1, 1) The syntax for 'AVERAGE' is incorrect. (AVERAGE(Sentiment[Score]))."
}
},
{
"code": "AnalysisServicesErrorCode",
"detail": {
"type": 1,
"value": "3238920194"
}
}
]
}
}
}

I would appreciate any kind of help or hint in what i am doing wrong, or if certain statemeent s are just not supported within the API etc.

BR Ruben

[skowronp123]

In the query text before any " will be used You need to add \. At list this worked for me in Azure Data Factory - spent some time on this also ... 

"query": "HERE !!!! "

[RubenSchilling]

Good morning,

i am not able to execute any queries via the Execute query REST API, except when i ONLY use evaluate.

As you can see this one works without problem:

{
"queries": [
{
"query": "EVALUATE VALUES(Manufacturer)"
}
],
"serializerSettings": {
"includeNulls": true
}
}

RESULT:

{
  "results": [
    {
      "tables": [
        {
          "rows": [
            {
              "Manufacturer[ManufacturerID]": 6,
              "Manufacturer[Manufacturer]": "Leo",
              "Manufacturer[MfgisVanArsdel]": "No"

But when i literally use any other query like this:

{
"queries": [
{
"query": "AVERAGE(Sentiment[Score])"
}
],
"serializerSettings": {
"includeNulls": true
}
}

The Result usually shows something like:

{
  "error": {
    "code": "DatasetExecuteQueriesError",
    "pbi.error": {
      "code": "DatasetExecuteQueriesError",
      "parameters": {},
      "details": [
        {
          "code": "DetailsMessage",
          "detail": {
            "type": 1,
            "value": "Query (1, 1) The syntax for 'AVERAGE' is incorrect. (AVERAGE(Sentiment[Score]))."
          }
        },
        {
          "code": "AnalysisServicesErrorCode",
          "detail": {
            "type": 1,
            "value": "3238920194"
          }
        }
      ]
    }
  }
}

I would appreciate any kind of help or hint in what i am doing wrong, or if certain statemeent s are just not supported within the API etc.

BR Ruben

[ronaldhiggins]

Based on the information you provided, it seems like there might be an issue with the syntax of the query you are using with the Execute Query REST API. The error message indicates that the syntax for the 'AVERAGE' function is incorrect.

The issue is likely related to the fact that the 'AVERAGE' function requires a table as an argument, not just a column name. In DAX (Data Analysis Expressions), which is the language used in Power BI for calculations, the 'AVERAGE' function needs to be used in combination with the 'SUMMARIZE' or 'FILTER' function to define the table from which the average should be calculated.

Here's an example of how you can modify your query to calculate the average of the 'Score' column in the 'Sentiment' table using the 'SUMMARIZE' function:

{
"queries": [
{
"query": "EVALUATE SUMMARIZE(Sentiment, \"Average Score\", AVERAGE(Sentiment[Score]))"
}
],
"serializerSettings": {
"includeNulls": true
}
}

In this query, we use the 'SUMMARIZE' function to create a new table that groups the data by the 'Sentiment' table and calculates the average of the 'Score' column. The result will include a new table with one column named "Average Score" containing the average value.

Remember to adjust the query according to your specific data model and the tables/columns you want to use.

If you need to perform more complex calculations or filtering, you can use other DAX functions like 'CALCULATETABLE', 'FILTER', 'ALL', etc., to manipulate the data before calculating the average.

If you continue to face issues, ensure that your data model is correctly set up in Power BI and that the column names and table names you are using in the DAX queries are correct.

If you're using the Execute Query REST API with a different tool or language, make sure to check the documentation and examples provided by the API to ensure you are using it correctly with your specific setup.

[AntrikshSharma]

@RubenSchilling Whether it is REST API, Power BI Desktop, Service, SSAS, Azure, DAX can be only queried by external tools using EVALUATE, that's what initiates the command.

[v-rzhou-msft · Accepted solution]

Hi @RubenSchilling ,

As far as I know, AVERAGE() should be an aggregation function which will return aggregated value instead of a table.

Datasets - Execute Queries Rest API will return data as a table. 

So Power BI will return error.

You may refer to this offical blog to learn more details.

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.