Forum Discussion
Kudo KingpinDAX Query from Dataset
I need to extract certain data from my existing Dataset (that sits in the service) into another PowerBI desktop.
Without live connection.
How do I run a specific DAX query from my Dataset?
I've heard there is a new REST API for this but I need to keep it simple within the Desktop.
Please help
Yeah, you can connect to REST APIs in Power Query, although it would likely be very bespoke M code and Power BI doesn't support OAuth 2.0 authentication. When I've done it in the past I've needed to stash a refresh/access token somewhere via Power Automate, not recommended for something like authenticating a Microsoft admin or service account
10 Replies
mattwwResponsive Resident
Hi michaelsh, I don't believe there is a way of achieving what you're looking for, once you connect to a dataset in the Power BI Service, that locks your abillity to create new measures.
The two options you describe are probably the recommended ones, connect to the dataset in Power BI Desktop which will give you access to all the existing DAX measures, or use the new API functionality to execute some DAX against the dataset.
Depending on how complicated the data is, you could create a Dataflow and connect to that, which would then allow you to write new DAX queries on top
- mattww
True, I was more thinking you could replicate the Power Query from your dataset into a Dataflow, to re-acquire the data from your source, then that would give you data hosted in the Power BI Service which you could pull into Power BI Desktop and add new measures too
Of course, that would be a table or several tables, without relationships or any DAX, so I'm not sure how applicable this is to what you're looking for
Otherwise, it does sound like the constraints you have mean it's not possible with the current capabilities of Power BI Desktop/Service
- Fowmy
Super User
michaelsh
You can extract the data and save it in a source like Excel or SQL then create a new data model from it from PBI Dektop but the data will be static, you need to reimport.
You can export the data from DAX Studio directly yo SQL : https://www.youtube.com/watch?v=WaUCYUHLHdw
However, I am not sure why you would need such a process, you can directly connect to a Power BI dataset from the service.
v-yingjlCommunity Support
Hi michaelsh ,
About DAX Query Rest API, I think it seems be used external instead of Power BI when you need to get the DAX formula firstly.
Refer:
- Announcing the public preview of Power BI REST API support for DAX Queries
- Power BI Blog: Power BI REST API Support for DAX Queries
Currently as far as I know, when connecting to Power BI datasets, there seems no direct way to get the DAX formula from the datasets instead of using external tools like DAX studio etc.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
michaelshBut maybe there is a way to run a DAX query with the new REST API ability directly from Power BI?
I mean, Power Query can work with APIs, so maybe it can work with PowerBI Service API as well?
- mattww
Yeah, you can connect to REST APIs in Power Query, although it would likely be very bespoke M code and Power BI doesn't support OAuth 2.0 authentication. When I've done it in the past I've needed to stash a refresh/access token somewhere via Power Automate, not recommended for something like authenticating a Microsoft admin or service account
