Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Best Regards,
Pragati Jain
LinkedIn | Twitter | Blog | YouTube
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Proud to be a Super User!!
Solved! Go to 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
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
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 🙂
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |