Difference between summarize table in Query Editor or SUMMARIZE

[Anonymous]

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 

[Anonymous]

Hi @Anonymous

• 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

[Anonymous]

---

@Anonymous wrote:

Hi @Anonymous

• 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)

[Anonymous]

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. 

 

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