Grouping data Performance - Query Editor vs DAX

[Pragati11]

Hello Power BI Experts,

I just wanted to understand the difference in the performance based on column calculations done using DAX versus that done in Query Editor. 

Let's consider creating a summarised table using an existing table.

DAX - I can use GROUPBY function to do this.

Query Editor - I can use Group By transformation to achieve this.

So, from performance point of you, where should this calculation sit in Power BI Desktop - Query Editor or DAX ?

NOTE: I am dealing with huge volume of data. (~ 12 GB)

Thanks in advance!

Pragati

[camargos88]

Hi @Pragati11 ,

I would handle modeling with Power Query.

If you do it using DAX, you are gonna have your original table on the model, with PQ it will reduce your amount of data.

Also, if you need to use this data for more than 1 report maybe it's better use DataFlow, with that only PQ.

Ricardo

[parry2k]

@Pragati11 it depends on how you want to consume the info. GroupBy DAX doesn't need to have a separate table, it can be used in the DAX expression, with Power Query, of course, you will get a separate table. It all boils down the end result you are trying to get it.

If you say, you want separate column, and should you use DAX or Power Query, I would say PQ and with tweaking in Group BY function in PQ can be faster but I would like to add here, group by, merge, sorting, etc are very expensive queries in Power Query and given the size of the dataset, it is going to be super slow. I would recommend having it grouped at source if your source is a relational database like SQL Server etc.

All in all, it depends what is the need of grouping and that will dictate the solution.

I hope it is helpful.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Pragati11]

Hello @parry2k ,

Thank you for your quick response.

My requirement is to create few calucated columns at the client level. Now in my current file I have almost 7 million rows and some other columns related to customer sales.

When I try to use the EARLIER function to write a type of calculations at the client level, the dax formula takes a long time to finish. So, I thought I'd create group-level calculations on an individual customer. But this greatly increases the number of tables in my data model and greatly affects Power BI performance.

Therefore, my question was where it is best to put the group at the client level by calculations.

You may need to think about moving calculations in the database until there is no other way to handle them.

Thank you

Pragati

[parry2k · Accepted solution]

@Pragati11 why calculated columns? I use more than 100 million rows of data and with measures, I was able to achieve anything and these were complex measures. First, why you are adding a column? Also, look at aggregation

I personally feel grouping is not a solution in this case.

[Pragati11]

Hi @parry2k ,

May be I am just thinking a lot and started creating columns. Looks like I should start creating measures rather than columns and see how it looks like.

Thanks for this suggestion. I will try this.

Thanks,

Pragati

[v-lili6-msft]

HI  @Pragati11 

If you need to transform and shape the data model, for example, remove unless columns or get a summary table, etc. you could do it in power query, and this will reduce the size of dataset too.

Then if you want to keep raw data, you need to use DAX to create a calculate column or calculate measure. But I suggest you use a measure instead of column.

See more details by this blog:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Regards,

Lin

[parry2k]

@Pragati11 sounds great. Good luck. Yeah, always put MEASURES first and every time you think of creating a column, ask yourself, why you need this? Cheers and good luck. Keep posted and let's know how it goes. 

[nandukrishnavs]

@Pragati11 

If we compare DAX and Query Editor, I would prefer the Query Editor.

It is better to use ETL tool to implement the same.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Greg_Deckler]

I wouldn't necessarily classify 12 GB of data as huge. But regardless, you generally want to push transformations up the chain as far as possible. So if you can do it in the data source = best then Power Query and then DAX. There are always exceptions but that's generally the case.