Forum Discussion

rjhale's avatar
rjhale
Helper V
5 years ago
Solved

New API Endpoint with DAX Queries

MS recently announced support for DAX queries against published Power BI datasets through a new api endpoint: Announcing the public preview of Power BI REST API support for DAX Queries | Microsoft Po...
Need Help
  • rjhale's avatar
    rjhale
    4 years ago

    I finally got this partially worked out with the help of one of the very helpful Power BI engineers.  For some reason datasets in My Workspace don't seem to be getting proper build permissions, so I decided to switch to just testing datasets in my v2 workspaces.  

     

    I initally thought that I had to use the format https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId} because that is what I'm accustomed to doing.  This will probably change as this gets closer to GA, but for now you actually just remove the parts about the group and reference the dataset directly.  For example: https://api.powerbi.com/v1.0/myorg/datasets/f388a618-2c93-41c5-8528-44c0c1129d70/executeQueries

     

    Once I made that change, I was able to return data from a Powershell script and Postman.  Still not sure about the permssions in My Workspace, but I'll likely never use that anway. 

jhayes0128's avatar
jhayes0128
Frequent Visitor
4 years ago

rjhale Anonymous Could you please post your full working code? I am also having the same issues but having a hard time following the fixes in this thread.

 

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 (same as yours):

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

 

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

 

I have tried removing the ' in the DAX. I have tried different urls. Same error every time. I'm losing it over here so any help you can provide is appreciated!!

 

rjhale's avatar
rjhale
Helper V
4 years ago

One thing I noticed is that you didn't escape your double quotes in query.  In the blog post they Kay mentions and don’t forget to escape quotation marks with a backslash (\”).  The latest query that I copy and pasted from Power BI Desktop looked like this:

DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'Proposals'[DOCUMENT_ID],
      \"SumDOCUMENT_DIRECT_AMT\", CALCULATE(SUM('Proposals'[DOCUMENT_DIRECT_AMT]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(501, __DS0Core, 'Proposals'[DOCUMENT_ID], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'Proposals'[DOCUMENT_ID]

 

Hope that helps.

  • jhayes0128's avatar
    jhayes0128
    Frequent Visitor
    4 years ago

    Same error 😥 Would you be able to post your $requestBody?

    $requestBody = @"
    {“queries”: [{“query”:”EVALUATE SUMMARIZE('Calendar','Calendar'[Year],\"Days in year\",COUNT('Calendar'[Date]))“}], “serializerSettings”:{“incudeNulls”: true}}
    "@
    • rjhale's avatar
      rjhale
      Helper V
      4 years ago

      The last query that I posted was actually from a Power Automate variable that I was working with.  I also tested a different query using Powershell, and here is an example of the $requestBody variable: 

      $requestBody = @"
{
    "queries":
    [
        {
        "query": "
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({\"CHEM\"}, 'Proposals'[DOCUMENT_DEPT_NAME])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'Proposals'[DOCUMENT_ID],
      __DS0FilterTable,
      \"SumDIRECT_AMT\", CALCULATE(SUM('Proposals'[DIRECT_AMT]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(501, __DS0Core, 'Proposals'[ID], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'Proposals'[ID]"
        }
    ]
}
"@@

      I would also make sure that your double quotes are the correct type of double quotes.  I'm not sure if you copy and pasted right from your PS script, but it looks to me like you have two different types of double quotes being used. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        And jhayes0128. I also noted that in your code, you are not only using straight quotes (sorry for the probably not fully correct term). You should use " and not “ or ”.
        Here is my simple request body:

         

        # Here comes the DAX query wrapped in the request body.
# Using @" "@ makes the text literal, which is much easier
# Not that " in the DAX query must be preceeded by a \
# Port Calls Query - List ship count per country code
$requestBody = @" 
{ 
    "queries": 
    [
        {"query":
            "EVALUATE SUMMARIZECOLUMNS(
                'Movements'[countryCode], 
                \"Ship Count\", [shipCount]
                )"
        }
    ], 
    "serializerSettings":{"incudeNulls": true}}
"@