cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
weiyu_chen
Frequent Visitor

What is the escape char for rest api execute queries in postman

POST Body:

{
"queries": [
{"query": "EVALUATE COUNTAX(FILTER('Activity',[Activity Status]=\"Completed\"), [Priority])"
}
],
"serializerSettings": {
"includeNulls": true
}
}
 
Got this error in Response:
{
"error": {
"statusCode": 0,
"errorCode": "QueryUserError",
"message": "Query (1, 1) The expression specified in the query is not a valid table expression.",
"errorDetails": [
{
"code": "AnalysisServicesErrorCode",
"detail": {
"type": 1,
"value": "3241803862"
}
}
]
}
}
 
Basically, this is my query: 
EVALUATE COUNTAX(FILTER('Activity',[Activity Status]="Completed"), [Priority])
How do I escape the '"' inside the body of the request. Is it '\', or something else? Thanks!
 
 

 

1 ACCEPTED SOLUTION

Hi @weiyu_chen ,

I checked some of the relevant restrictions and it seems that none of them are touched on your side...

  1. Only 1 DAX query can be executed per REST API request.
  2. Each DAX query can have only 1 result set.
  3. Result sets are capped at 100k rows.
  4. Supported data types: string, numeric, boolean, blank, datetime and variant. Binary is not supported.
  5. Azure AS and SSAS do not support this DAX REST API.

Is it possible that the error is reported because this DAX Query returned a single value, but the required return result here is a table? Change the DAX Query to the following form and see if it works:

{
"query": "EVALUATE SUMMARIZE (
    'Activity',
    \"countofpriority\", COUNTAX ( FILTER ( 'Activity', [Activity Status] = \"Completed\" ), [Priority] )
)"
}

Best Regards

Community Support Team _ Rena
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

9 REPLIES 9
yingyinr
Community Support
Community Support

Hi @weiyu_chen ,

According to this official blog, its escape character is a backslash (\”). So I think your initial writeup should be correct...


@weiyu_chen wrote:

POST Body:

{
"queries": [
{"query": "EVALUATE COUNTAX(FILTER('Activity',[Activity Status]=\"Completed\"), [Priority])"
}
],
....

yingyinr_0-1632303046912.png

Please check if you enable Allow XMLA endpoints and Analyze in Excel with on-premises datasets under the tenant-level setting tab in the Power BI admin portal. Besides that, you need the Build permission for the dataset if you want to query by using this new REST API.

yingyinr_1-1632303070490.png

yingyinr_2-1632303100260.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@yingyinr Thanks for the reply. Yes, I have enabled XMLA:

Screen Shot 2021-09-22 at 8.34.18 AM.png

Also I am the owner of the dataset. I can access it. The problem is within FILTER function, I have this issue. But in the following query in SUMMERIZE function, it's OK:

{
"queries": [
{"query": "EVALUATE SUMMARIZE(Account, Account[Owner], \"Total\", SUM(Account[Annual Revenue]))"
}
],
"serializerSettings": {
"includeNulls": true
}
}
This query runs fine and return correct resultset.
Thanks!

Hi @weiyu_chen ,

I checked some of the relevant restrictions and it seems that none of them are touched on your side...

  1. Only 1 DAX query can be executed per REST API request.
  2. Each DAX query can have only 1 result set.
  3. Result sets are capped at 100k rows.
  4. Supported data types: string, numeric, boolean, blank, datetime and variant. Binary is not supported.
  5. Azure AS and SSAS do not support this DAX REST API.

Is it possible that the error is reported because this DAX Query returned a single value, but the required return result here is a table? Change the DAX Query to the following form and see if it works:

{
"query": "EVALUATE SUMMARIZE (
    'Activity',
    \"countofpriority\", COUNTAX ( FILTER ( 'Activity', [Activity Status] = \"Completed\" ), [Priority] )
)"
}

Best Regards

Community Support Team _ Rena
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

@yingyinr Thanks a lot for the solution.

weiyu_chen
Frequent Visitor

@lbendlin 

Thanks for the reply. I have tried your suggestion, get the following error:

{
"error": {
"code": "BadRequest",
"message": "Bad Request",
"details": [
{
"message": "After parsing a value an unexpected character was encountered: \". Path 'queries[0].query', line 3, position 69.",
"target": "request.queries[0].query"
},
{
"message": "'request' is a required parameter",
"target": "request"
}
]
}
}
 
The query body is:
{
"queries": [
{"query": "EVALUATE COUNTAX(FILTER('Activity',[Activity Status]=""Completed""), [Priority])"
}
],
"serializerSettings": {
"includeNulls": true
}
}
 
Thanks!
lbendlin
Super User
Super User

You can try

 

"query""EVALUATE COUNTAX(FILTER('Activity',[Activity Status]=""Completed""), [Priority])"

@lbendlin 

Also tried: 

"query": "EVALUATE COUNTAX(FILTER('Activity',[Activity Status]='"'Completed'"'), [Priority])"
get the error:
{
"error": {
"code": "BadRequest",
"message": "Bad Request",
"details": [
{
"message": "After parsing a value an unexpected character was encountered: '. Path 'queries[0].query', line 3, position 70.",
"target": "request.queries[0].query"
},
{
"message": "'request' is a required parameter",
"target": "request"
}
]
}
}
Thanks!
Weiyu

Can you confirm that your original query runs in DAX Studio?

@lbendlin I have run the following query (measurement) in Power BI desktop without any issue:

Subcategory Cound = COUNTAX(FILTER(ProductSubcategory,[ProductSubcategory] = "Fans"),[ProductSubcategoryKey])

Thanks!

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors