DAX Info Functions Against Published Datasets

[rjhale]

Hello,

As part of a Power Automate Flow that we setup months ago, we were using the new DAX Info functions to pull metadata regarding partitions and expressions.  These queries have been working without issue until this past weekend.  Here is an example of a simple DAX query that would return information about the queries loaded to the data model:

For the last couple of days, these have failed to execute with a generic error stating "Failed to execute the DAX query" and an Analysis Services Error Code of 3239575574.  We have a lot of other Flows that run DAX queries against a dataset in this manner, and those have had no issues.  

Any ideas what may be preventing these queries from executing properly? 

[mariussve1]

Hi,

I have also checked today, but still not working. We are using this to autodocument all our models across tentant. Really important to get this up and working again!

Any news from Microsoft?

[drewgillson]

Great news! Thank you to the team

[nigeldavidmaxey]

Does anybody know if MS announced this change, or published any changelog and/or reasoning. This has disabled a production job. Does anybody know of an official channel to raise a support ticket?

[Nicke]

I got this yesterday from Microsoft if anyone is interested:

I wanted to send a gentle follow-up to let you know that our product team is actively working on a possible solution for long term mitigation. We understand how important this issue is to you, and we are committed to resolving it as soon as possible. We will provide you with any updates as they become available.

Thank you for your patience and understanding.

[drewgillson]

Thanks I appreciate the visibility I think there are many people blocked here who don't have an open conversation with support

[EricLiu]

We got similar error when passing below query to Execute Queries REST API, it worked well and suddenly failing recently

{
  "queries": [
    {
      "query": "SELECT * FROM $SYSTEM.TMSCHEMA_MEASURES"
    }
  ]
}

[rjhale]

It seems that they're pushing us to XMLA endpoint queries for the time being.  I'm exploring this option now, but I'm running into another issue related to pulling that content. 

I have a table with a field for Workspace and field for Dataset name.  I'm trying to create a function that I can pass those in as parameters and then return the partitions for each dataset like so:

(Workspace as text, Dataset as text) as table =>
    let
        Query = "EVALUATE SELECTCOLUMNS(INFO.PARTITIONS(), [QueryDefinition])",
        Source = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/" & Workspace, Dataset, [Query=Query, Implementation="2.0"])
    in
        Source

 This works fine locally, but cannot be refreshed because of the dynamic content.  The error we get in the service is "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed."

Any thoughts on how I can work around this problem?  

[GNSM92]

Hello,

INFO Dax functions are blocked in Execute Query API. Microsoft confirmed the same in my support ticket. MS product team mentioned that it was blocked because of security concern 

Thanks

[Preenz]

We are also using INFO to get metadata for all columns and tables but unfortunately this stopped to work til this date.. Hopefully this gets fixed very soon

[Nicke]

I got this from support:

Thank you for your response, and I apologize for any confusion.

We have received an update from the team indicating that the INFO functions depend on Dynamic Management Views (DMVs), which are not supported by the Execute Queries REST API. However, we understand that customers were able to use these functions before the recent update to the Execute Queries REST API.

The changes are considered to be by design, and we won't support the use of INFO.* within Execute Queries going forward. However, our product team is diligently working on a fix to support the INFO family of functions with the Execute Queries REST API in the near future, although we don't have an exact timeline yet.

Please try using the workaround suggested by our product team and let us know the outcome. Once again, we sincerely apologize for any inconvenience this may have caused.

Thank you once again for your understanding. If you have any further questions or need assistance, please don't hesitate to reach out.

[mark_carlisle]

Monumentally frustrating this. We have a PowerApp that use these functions to get the list of tables and partitions so we can selectively refresh them instead of the whole model.

[Nicke]

Another answer,

We truly understand the challenges you are facing, and we sincerely apologize for any inconvenience this situation has caused.

Yes, as our team mentioned, they are working on a fix to support the INFO family of functions with the Execute Queries REST API in future. While our team is dedicated to resolving this issue as swiftly, we unfortunately do not have a precise timeline for when this fix will be implemented.

We appreciate your patience and understanding.

[rhartop]

Thanks Nicke.

What was the workaround suggested by the product team? 

[Nicke]

Here is the previous answer, not a viable option for us unfortunately.

We got an update from our product team that a recent update to the Execute Queries REST API has caused DAX queries using the INFO family of functions to return HTTP 400 or similar errors. We recognize how disruptive this can be to your workflow and sincerely apologize for inconvenience this may have caused.

The INFO functions depend on DMVs, which the Execute Queries REST API has not supported. However, we acknowledge that customers were able to use these functions prior to the recent update.

It's clear from Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn that "Only DAX queries are supported at this time. MDX and DMV queries are not supported." so this is a restriction that was not previously implemented.

As a workaround, if your model is in premium, you might be able to switch your solutions to XMLA Endpoint-based connections and query semantic models using the AS client libraries (ADOMD.NET).

We appreciate your patience and understanding as we work through this issue. If you need further assistance or have any questions, please do not hesitate to reach out. We are here to help.

[rjhale]

That's interesting.  So if I'm understanding this correctly, they plan to simply drop support for something they advertised as a new feature in the Power BI service only 6 or 7 months ago?  This is incredibly frustrating if that's the case. 

Did they provide any guidance on how to query semantic models using the AS client libraries?  It would be helpful if there was some way to do this through Power Automate as our previous solution was executing those Info queries from a scheduled Flow.  

[marcio_fornari]

😡, ok Microsoft, you stop the work the function and it's ok? 

[marcio_fornari]

Same error here, Come on microsoft, fix it! 

[rjhale]

I have an incident open with Microsoft, and I'll be meeting with one of their PBI engineers early next week.  I'll post back here once we get more info.  

[rhartop]

Same problem too

[Nicke]

I have the same issue too. Does anyone has any clue why and what to do? Is it a bug or a deprecation?

[hmayer156789]

Same Problem here - we had a DAX query with InfoFunctions to get the last refresh of a Table running for Month running via REST API on Pruduction and today it started failing