topic Re: PowerBI API - Execute Queries on Dataset - Problem with DAX Statement in Developer

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

RubenSchilling — Thu, 27 Jul 2023 14:45:41 GMT

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

REST API - Execute Queries not working as expected

RubenSchilling — Thu, 27 Jul 2023 12:25:45 GMT

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

Re: REST API - Execute Queries not working as expected

AntrikshSharma — Sat, 29 Jul 2023 10:07:18 GMT

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

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

Anonymous — Mon, 31 Jul 2023 09:17:41 GMT

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.

Re: REST API - Execute Queries not working as expected

ronaldhiggins — Tue, 15 Aug 2023 15:34:43 GMT

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.

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

Anonymous — Tue, 29 Aug 2023 08:01:37 GMT

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