Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ZLJ
Advocate II
Advocate II

Which is the best place to perform calculations?

Hi

Can someone explain to me the best place to perform calculations / transformations?

i.e. If I want to concat 2 columns should I

1 - do it in the original SQL query

2 - do it in the Edit Query applied Steps

3 - do it in the Report Data view

 

I'm used to writing SQL, so feel more comfortable doing most of the calculations in the SQL Query, and just using the Report data view calculations for merging data from multiple sources.  Just not sure if there is an advantage to using the Edit Query Steps instead, and what benefit it has over leaving the calculations till the Report Data view stage.

 

Thanks

 

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
Microsoft Employee

@ZLJ

 

I'd partially agree with @Greg_Deckler that the 3 approaches affect the load distribution.

 

Regarding "Report Data view", from what I observed, when using SQL Server, in Import mode, the behavior is different from in the mode DirectQuery. In DirectQuery mode a measure would query the database directly (as below snapshot) while in Import mode the PBI desktop itself handles the measure.
Capture2.PNG

Regarding "Edit Query", powerbi desktop just re-forms the query(regardless of the mode) and the load is still mainly on SQL Server. A merge queries option in Edit Query sent the re-formed query to SQL Server as I observed.
Capture.PNG

So in one word, the DAX would distribute load according to Import or DirectQuery mode. "Edit Query" always put load on SQL Server.

That's what I observed.

View solution in original post

4 REPLIES 4
ZLJ
Advocate II
Advocate II

Thanks for the feedback everyone.  (Sorry for the slow reply, i've been on holiday / offline).  I think i understand a bit more about the difference between them now.

So sounds like there is no right answer, and i should just go for what works for the situation. 

Eric_Zhang
Microsoft Employee
Microsoft Employee

@ZLJ

 

I'd partially agree with @Greg_Deckler that the 3 approaches affect the load distribution.

 

Regarding "Report Data view", from what I observed, when using SQL Server, in Import mode, the behavior is different from in the mode DirectQuery. In DirectQuery mode a measure would query the database directly (as below snapshot) while in Import mode the PBI desktop itself handles the measure.
Capture2.PNG

Regarding "Edit Query", powerbi desktop just re-forms the query(regardless of the mode) and the load is still mainly on SQL Server. A merge queries option in Edit Query sent the re-formed query to SQL Server as I observed.
Capture.PNG

So in one word, the DAX would distribute load according to Import or DirectQuery mode. "Edit Query" always put load on SQL Server.

That's what I observed.

Just a heads up,

 

Edit Query won't always put the all of the load on the server. It depends on the steps applied and whether they can be folded back. If they cannot all be folded back then the query engine (M) will handle the rest. So there is a bit of trial and error/art to desiging optimal queries if you are trying to have the query folded back to the server.

Greg_Deckler
Super User
Super User

I think this really boils down to where you are putting the load. In a SQL query, you are putting the load on the SQL Server, with doing it in the query, you are putting the load on the Desktop and the gateway and in the report data view, that would be the Desktop and the Service. Is there an advantage one way or the other? Eh, perhaps. You could make an argument that if you are doing a bunch of heavy calculations that it is better to offload that from your SQL Server to basically cloud-based resources, once you upload the Desktop file to the Service. There are also some really powerful functions that you can leverage in "M" (query) and DAX like a lot of the time intelligence stuff. I tend to follow the mantra of using what you are comfortable with as well as trying to do as much in one place versus having calculations spread out between SQL, "M" and DAX. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors