cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adetogni Senior Member
Senior Member

Difference between summarize table in Query Editor or SUMMARIZE

Hi, a simple question.

For my analysis I have a main table and a summarized version of it.
I have two options:

1. clone the main table in query editor and make the grouping in there

2. use the SUMMARIZECOLUMS in DAX to group data in modeling

 

The only difference that I can spot is that with the option 1, when I update the dataset, the data is loaded two times fully, so time to update is longer.


Is there any other difference in terms of performances? Thanks 

3 REPLIES 3
rajendran Super Contributor
Super Contributor

Re: Difference between summarize table in Query Editor or SUMMARIZE

Hi @adetogni

 

  • When you load the table in Query Editor, you are bringing the data physically to the model, so the size of your report increases.
  • Also, duplicating the source will result in another DB hit. ( You can use reference option to avoid this)
  • In other hand, DAX is dynamic . If you use it in calculated table/ column, it will be calculated once the model is loaded
  • If you use the calculation in Measure, it is dynamic and doesnt store physically .

 

Thanks

Raj

adetogni Senior Member
Senior Member

Re: Difference between summarize table in Query Editor or SUMMARIZE


@rajendran wrote:

Hi @adetogni

 

  • When you load the table in Query Editor, you are bringing the data physically to the model, so the size of your report increases.
  • Also, duplicating the source will result in another DB hit. ( You can use reference option to avoid this)
  • In other hand, DAX is dynamic . If you use it in calculated table/ column, it will be calculated once the model is loaded
  • If you use the calculation in Measure, it is dynamic and doesnt store physically .

 

Thanks

Raj


Hi Raj

 

What is the reference option you mention to avoid re-loading the same table?

Re calculation, i know the difference between a calculated column and a measure, but that's not hte question. The question is the difference between cloning the *table* in powerquery or creating a new table through DAX (*not* calculatetable - which is a runtime measure, but table=SUMMARIZE)

Highlighted
rajendran Super Contributor
Super Contributor

Re: Difference between summarize table in Query Editor or SUMMARIZE

When you right click the table in power Query, you can see the below option. Use this Reference option to clone the table, which will avoid the second DB hit. 

 

Reference.PNG 

 

In my understanding - Creating a summary table in dax and in Power Query using Reference , both consumes same memory but doing it in DAX using SUMMARIZE is flexible and suggested best practice.

 

Hope this helps.

 

Thanks

Raj

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 284 members 3,112 guests
Please welcome our newest community members: