Forum Discussion
Super UserGrouping data Performance - Query Editor vs DAX
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.
Super UserPragati11 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
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.
