cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adetogni Member
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
Super User
Super User

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 Member
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
Super User
Super User

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