How to automate the extraction desire data from Visual through performance Analyzer DAX Query

[mh2587]

Hi PBI Community 
I hope you people will be great, I have a scenario, Can we make some sort of GUI in which the end user will extract the data from visual simple that we often copy the DAX Query from the Performance analyzer and then paste it into dax studio through which we are able to export the data in bulk size, I am just curious how to automate this process is there any way to make an application through which end user just have a button and click it to export the data 
My English is not good, I hope I explain the scenario well  

[sroy_16]

Hello,

You can try creating a custom application Using Python with Tkinter and Power BI REST API.

I am trying to post the code as well and see if this would work for you or not.

import tkinter as tk
from tkinter import messagebox
import pandas as pd
import requests

class PowerBIExporter:
def __init__(self, root):
self.root = root
root.title("Power BI Data Exporter")

self.label = tk.Label(root, text="Click to export data")
self.label.pack()

self.export_button = tk.Button(root, text="Export Data", command=self.export_data)
self.export_button.pack()

def export_data(self):
try:
# Define Power BI API URL and authentication details
powerbi_url = 'https://api.powerbi.com/...'
headers = {'Authorization': 'Bearer YOUR_ACCESS_TOKEN'}

# Fetch data using Power BI REST API
response = requests.get(powerbi_url, headers=headers)
data = response.json()

# Convert data to DataFrame and export to CSV
df = pd.DataFrame(data['value'])
df.to_csv('exported_data.csv', index=False)

messagebox.showinfo("Success", "Data exported successfully!")
except Exception as e:
messagebox.showerror("Error", str(e))

if __name__ == "__main__":
root = tk.Tk()
app = PowerBIExporter(root)
root.mainloop()

##This is a Python code 

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

[smpa01]

@mh2587  I have an exact similar req, did you make any progress on this by any chance?

@Phil_Seamark  is there any way for me to automatically extract the dax query that builds a particular viz in a report residing in a premium workspace.

The report is based on a semantic model controlled by a different team. So the same viz can have different calculation for same measure, different dimension columns, different viz level filters between today and tomorrow. I have read write access to the semantic model but have no control how it is built / edited.

I simply need to access the data that gets built through the dax query, hence I need to access the dax query to build the daily data without needing to do it a manually extract it on daily basis with Perf Analyzer. (Exporting viz data is not an option. I dont't know if there is an API that can extract all the rows as Exporting has row limitation)

Is there any way to do it? Is there any API that can help with this?

[lbendlin]

- use the query log if you have access to Log Analytics (and are willing to pay the extra cost)

- use the "Excel with Live connection"  export option on the visual. That will give you the exact DAX query for the visual in its current filter context.

[smpa01]

@lbendlin  Thanks for this.

++  @AlonBAR @GilbertQ 

Is there any way to for me utilize export-to-file API to export to Excel with Live connection

#2nd option below

I can do this for PDF

$requestBody = @"
{
    "format": "PDF",
    "powerBIReportConfiguration": {
        "pages": [
            {
                "pageName": "ReportSection1234567890"
            }
        ]
    }
}
"@

[lbendlin]

You are mixing concepts.  The API you reference is for rendering.  What you want to get is the raw data.

[smpa01]

I use this API to automatially export from 10+ reports (each containing 3+ pagess and 10+filters) to extract 10 X 3 X 10 =300 + pages of pdf on daily basis. Similarly, it can also export excel, csv and other formats. I am simply asking can I use this API to extract the excel with live connection. If yes, what should go in the request body?

[lbendlin]

The answer is no. Exporting to Excel is only supported for paginated reports.

[mh2587]

@anyone

[lbendlin]

Ask the end user to export the visual's data to "Excel with live connection".  It is not actually a live connection, instead it is an XMLA connection that includes the DAX query.  You can then analyze the DAX query in the Excel connection properties. After you have the query you can create a Power Automate flow that runs it for you, and that delivers the results to where you need them.  You could even include a Power Automate visual in your Power BI report that would initiate that flow but the UX will be bad.

[mh2587]

I know one of the most straightforward and efficient way of exporting more than 1m rows in Power BI is just to copy the DAX query from Performance Analyzer, and paste it to DAX studio and export. This works and is fast.

However, suppose my users are business users, and we want to avoid having them do these manual steps, is there a way to automate this process via a button on power bi frontend? Like in a click of a button, capture the dynamic dax query, and export?

[mh2587]

But it still requires to copy the dax query from the performance analyzer and then pasting it in power automate's query against dataset body I want to automate the whole process for the end users as they are not technically sound

[lbendlin]

But it still requires to copy the dax query from the performance analyzer

Not from the Performance Analyzer - from the exported Excel file.  You can remove the silly TOPN filter there.

I think you are wasting your time focusing on the wrong problem. Your users are supposed to gain business insights from your Power BI reports, not to "fight the API"  extracting data.  If they need the source data then give them easy access to the source data.

[mh2587]

Thanks for the suggestion will discuss it

[mh2587]

@amitchandak @Greg_Deckler @lbendlin Need help regarding the Scenario, please