how can I get DAX query generated by power bi report?

[rpeleg]

Hi eb

I need to have a look at the full DAX query generated by power bi report (to get its data from underlying model).

Is there a 'show my DAX query' option in desktop?

If not is it hidden somewhere within the PBIX file?

I understand that looking at sql profiler will not cut it as DAX isnt registered over there but some kind of vertiPaq sql dialect??

TIA!

Rea

[tjd · Accepted solution]

Okay, by that I guess you mean the query for measures and calculated columns?  One way to do that is to click on each measure in PowerBI and the formula will appear in the formula pane above the canvas or table.  The other way is to use DAX Studio and hook it up to your PowerBI Desktop pbix.  

[Vickar]

If you are wanting to see the DAX Query generated by a visual[s] in a report published on PBI Service that is based on a Power BI dataset, you can download the report from PBI Service and open it in the PBI Desktop and then use th Performance Analyzer to trace the DAX Query generated. hope that helps.

[JasonPad]

Check this one in GitHub, it works great.

https://github.com/JasonPad19/PowerBIScanner/blob/main/README.md

[natabird3]

Actually if you go to View-> Performance Analyzer (in power bi dekstop) you can start recording and copy the dax from your visual and recreate in power bi report builder. This will copy all your measures and values, so its better in my opinion. 

[Anonymous]

The visual way folow the numbers 😀:

[Phil_Seamark]

Hi @rpeleg

SQL Profiler will work and you should be able to extract the DAX.  Do you have SQL Profiler installed and have you used it against a SQL Server Database engine?

[rpeleg]

Yeap got the profiler and worked with it.

I guess I got the wrong impression reading Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services

especially around pages 26-28 where they show an example of profiling DAX that looks like some kind of strange SQL...

[Phil_Seamark]

If we are looking at the same thing, I don't think that is DAX.  When using Profiler, remove all your events apart from Query Begin and Query End.  If the DAX is all you are after you won't need any other events.

[rpeleg]

thanks a lot will check it out ASAP

[tjd]

Perhaps I'm missing something here.  Why not just open the Advanced Editor on the Query ribbon for each of your queries to see what they say?

[rpeleg]

Thanks

I need DAX query generated by report not queries to model's data sources.

[tjd · Accepted solution]

Okay, by that I guess you mean the query for measures and calculated columns?  One way to do that is to click on each measure in PowerBI and the formula will appear in the formula pane above the canvas or table.  The other way is to use DAX Studio and hook it up to your PowerBI Desktop pbix.  

[rpeleg]

I want the full EVALUATE() story.

Not just measures and formulas.

Would that be visible by the dax studio?

[tjd]

Not sure what you mean by the "full Evaluate() story".  I would suggest going to DAX Studio and reviewing how it works and what it shows to see if it answers what you're looking for.

[rpeleg]

thanks a bunch!

DAX studio seems worth  a try.

[Anonymous · Accepted solution]

I wanted to generate a DAX query using a drag and drop interface and found SQL Serer Report Builder quite handy as you can generate the query using a drag and drop interface and then just copy the DAX and use it wherever you like.