Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RubenSchilling
New Member

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

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

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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. 

vrzhoumsft_0-1690794969267.png

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.

View solution in original post

5 REPLIES 5
skowronp123
Frequent Visitor

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
New Member

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

 

 

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.

@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
Community Support
Community Support

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. 

vrzhoumsft_0-1690794969267.png

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.