Querying Power BI Datasets by DAX through Admin API

 At the end of last year, a new option appeared in the Power BI REST API (Admin), which allows sending DAX queries against a specific dataset. The JSON with the response then returns up to the 100,000 table rows. This allows you to use DAX queries through Power BI REST API in several exciting scenarios. Here I prepared one ... 

Scenario

Imagine a learning application used in your company where you learn about a certain topic, and then you are tested. Answers are recorded, so L&D knows what you have done and how you stand. Let's face it. We know a lot of such apps.  

But those who usually keep us with us to fulfill them for the next days/weeks must not end with just answering and getting the "RIGHT" or "WRONG" response. They usually remind us regularly and try to bring us back. They often try to achieve this with gamification elements, such as comparison participants or just information about your success over a while. #PowerBI can provide the calculation of results for this "gamification". For example, we obtain data using #PowerAutomate, distributing them to colleagues (students). 

Limitations

To be able to achieve this data acquisition, we must keep in mind all the limitations that result from the official documentation of this API call.

Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Docs 

Prerequisites – Azure

It is also necessary to decide whether, for this call, it will act as a user or as an application. I am inclined to say that we should make such calls as applications to show the following procedure. First, according to the documentation, we must set API permissions for our application for either Dataset.ReadWrite.All or Dataset.Read.All. Don't forget to have them approved by the admin.

In the detail of the application, we will immediately copy the data that we will then need. 

Tenant_Id & Client_Id: Overview

Client_Secret: Certificates & secrets

Prerequisites – Power BI Service

Using the Power BI API services through the application must not forget that this option must be enabled in Power BI Admin Portal. 

The functionality we require is also a separate setting in Power BI Admin, which must be enabled.

You can also query "My workspace" (if you are accessing it as a user). In general, however, you can only query data sets to which the entity (user, application) has permissions. As described in the documentation in the section about limitations. For this reason, I need to add the application to the workspace/dataset where I want it to query and copy its ID.

Let's bring the Automate.

After the Recurrence trigger, I prepared an initial to compose, storing all necessary values.

The following Parse JSON step creates records from the values prepared in this way, which I can then use across the entire flow.

We miss the last important information to query directly into the verification token model. While this may seem straightforward, it is crucial for what source you generate it. In this case, and always concerning the Power BI REST API, verification against the source https://analysis.windows.net/powerbi/api.

We parse the obtained answer using the classic Parse JSON method, and we get something that looks something like this.

{
  "token_type": "Bearer",
  "expires_in": "3599",
  "ext_expires_in": "3599",
  "expires_on": "1641987377",
  "not_before": "1641983477",
  "resource": "https://analysis.windows.net/powerbi/api",
  "access_token": "x........................x"
}
        

Combining the obtained token_type and access_token will give us the exact key that we can use for Authorization to query the Dataset. Please note in advance that the life of this token is limited. So don't try to make many calls on one token.

concat(body('tokenReciever')?['token_type'],concat(' ',body('tokenReciever')?['access_token']))

        

The call is made against the following address "https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries", where we insert the obtained token in the header and the query we requested in the body. Here, in this case, I'm getting data for one specific table, so EVALUATE {tableName} is enough for me.

{
  "results": [
    {
      "tables": [
        {
          "rows": [
            {
              "lastWeek[Email]": "stepan.resl@databrothers.cz",
              "lastWeek[Score]": 3,
              "lastWeek[MaxScore]": 7,
              "lastWeek[SuccessRate]": 0.4285,
              "lastWeek[Index]": 1
            }
          ]
        }
      ]
    }
  ]
}
        

I will parse the obtained answer again. Since Power Automate always sees only two levels of immersion, so in order to get directly to the data, it is necessary to make the first Apply to each, to which we can pass the second level or "tables." It automatically produces the first level of "results" on top of each other. I can call the Post Adaptive card in a chat or channel action in the second level. Here comes the time for creativity. The adaptive card may look different. But if you would like to be inspired, there is a public library.

>>  https://adaptivecards.io/samples/SportingEvent.html 

I was also inspired here, and I modified the card used in the library to display the match results. Now shows the personalized results of the learning application.

Summary

It is now straightforward to query the data that Power BI has processed, and once the path is created for the first time, it is already very brief. The capabilities that this API call provides in itself push the possible interconnection between Power Platform applications a step further. In the same way, gates open that Power BI may perceive as an integration center to which they will query. Even one Dataset can query the other Dataset in this form without DirectQuery. I am very curious about what ideas others will come up with.