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. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi rjhale 

Code format looks like as below.

{ “queries”: [{“query”:”<Your DAX Query>“}], “serializerSettings”:{“incudeNulls”: true}}

Please add the permissions for read and write in API permission in Azure Portal and see if the script then works.

Please check whether you have enable the permission in Admin portal in Power BI Service. 

Please check whether you have Build permission to this dataset and check whether this workspace is V2 instead of V1.

Current limitations in a nutshell:

  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.

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.

  • rjhale's avatar
    rjhale
    Helper V
    5 years ago

    Thanks for the reply.  I'm using the PowerShell cmdlets, and the documentation states "The Power BI Management cmdlets have the required app permissions to use the DAX REST API".  I don't think I should need adjust any App permissions in Azure because of this.  

     

    We have verified that the Allow XMLA Endpoints and Analyze in Excel setting is enabled for the entire organization.  Also, the account I'm using is the owner of the dataset, so it definitely has build permissions.  I've also tried publishing the dataset to "My Workspace" and a v2 workspace.  It doesn't seem to matter which workspace it's published to.  I get the same error regardless.  

     

    Any other ideas or suggestions?

     

  • rjhale's avatar
    rjhale
    Helper V
    5 years ago

    Is there any chance this is only available to first/targeted release tenants or users?  It didn't state it anywhere in the blog post, but I just tested the same dataset in our test tenant and I was able to connect from the Powershell cmdlets.  I'm almost certain that our test tenant is first/targeted release. 

     

    Update: I'm not sure that would be the case either as my account has the targeted options enabed in both our test and production tenants.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I have exactly the same kind of issue as you rjhale. Same error message.
      I have also verified that the Rest API is enabled in the admin portal and that I have build access to the dataset.

      $requestUrl = 'https://api.powerbi.com/v1.0/myorg/datasets/e500cd5d-4e48-4feb-80d5-33435a5c9a16/executeQueries'

      $requestBody = @"
      { “queries”: [{“query”:”EVALUATE SUMMARIZECOLUMNS('Movements[countryCode]', \"Ship Count\", [shipCount])“}], “serializerSettings”:{“incudeNulls”: true}}
      "@

      Write-Host $requestBody

      Write-Host 'Logging in'
      Login-PowerBI
      Write-Host 'Logged In'
      $result = Invoke-PowerBIRestMethod -Method POST -Url $requestUrl -Body $requestBody
      Resolve-PowerBIError -Last
      Write-Host 'Invoked'
      $result
      $parsed = $result | ConvertFrom-Json
      $parsed.results[0].tables[0].rows | Format-List
      Write-Host 'Done'

       

      I get the following response:


      Logged In
      Invoke-PowerBIRestMethod : One or more errors occurred.
      At C:\Users\lchh\OneDrive - Bunker Holding Group\PowerShell\SampleDaxRestAPI.ps1:26 char:11
      + $result = Invoke-PowerBIRestMethod -Method POST -Url $requestUrl -Bod ...
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
      + FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

      Exception : System.Net.Http.HttpRequestException: Response status code does not indicate success: 400 (Bad Request).
      at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
      at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__35.MoveNext()
      InnerException : False
      Message : Response status code does not indicate success: 400 (Bad Request).
      StackTrace : at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
      at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__35.MoveNext()
      HelpLink :
      Source : System.Net.Http
      ErrorDetails :
      ErrorCategory : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
      InvocationInfo : System.Management.Automation.InvocationInfo
      ScriptStackTrace : at <ScriptBlock>, C:\Users\lchh\OneDrive - Bunker Holding Group\PowerShell\SampleDaxRestAPI.ps1: line 26

      Invoked
      Cannot index into a null array.
      At C:\Users\lchh\OneDrive - Bunker Holding Group\PowerShell\SampleDaxRestAPI.ps1:31 char:1
      + $parsed.results[0].tables[0].rows | Format-List
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [], RuntimeException
      + FullyQualifiedErrorId : NullArray

      Done


      Anonymous is there anyway that this is not rolled out to our Power BI tenants?

      • rjhale's avatar
        rjhale
        Helper V
        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.