Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I have been exploring DAX Studio to gain a better understanding of what happens under the hood in a Power BI report. I'm curious about why an SQL query is generated when a DAX formula is executed.
Additionally, I've encountered the terms "DAX Formula" and "DAX Query" being used interchangeably in some resources, while in others, there seems to be a distinction between them. Please correct me if I'm mistaken. As far as I understand it, a "DAX formula" is what you write as a measure. In other words, "DAX Formula = DAX Measure."
On the other hand, a "DAX Query" is what generates the DAX formula. Are these the "Evaluate" and "Define" keywords we see in DAX Studio?
Also, why is there an SQL query generated with every DAX measure (or is "DAX Query" the correct term) ?
Solved! Go to Solution.
Eventually, yes. It will return a table (or multiple tables) to the renderer, indicating which values should go where (especially if row or column totals are involved).
This means for you as a visual designer that you want to be frugal with the number of fields you put into a visual. It might be tempting to load up the tootips aea (for example) but that all adds to the query cost.
Here is a sample data I created using the EnterData feature in power bi and a simple sum measure
Looking into DAX studio this is what I get
So it does look like there is an SQL query running in the back ground. I haven't used any power query transformations in this nor is it Direct query
I thought query folding kicks in only when the datasource is some sort of a database such as an SQL server
Ah, ok, you are looking at the VertiPaq queries. Yes, the DAX queries are internally translated into SQL-like queries against the SSAS Tabular storage. But these are purely internal and not something you can write directly.
thank you. And what about the code that DAX Studio generates ? That is the query which creates the visual ?
Eventually, yes. It will return a table (or multiple tables) to the renderer, indicating which values should go where (especially if row or column totals are involved).
This means for you as a visual designer that you want to be frugal with the number of fields you put into a visual. It might be tempting to load up the tootips aea (for example) but that all adds to the query cost.
thank you. That clears things up. I am sure I will have more questions as I dive deeper into DAX studio. Until then, I will mark this as solved
I'm curious about why an SQL query is generated when a DAX formula is executed.
Read about Query Folding
As far as I understand it, a "DAX formula" is what you write as a measure. In other words, "DAX Formula = DAX Measure."
That is incorrect. A DAX formula in the vast majority of cases returns a table. A measure can only return a scalar value.
Also, why is there an SQL query generated with every DAX measure (or is "DAX Query" the correct term) ?
Not with every query. Only with Direct Query and Query folding.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |