This topic contains 3 replies, has 2 voices, and was last updated by 
-
Posts
-
Hi I have a large database and I’m trying to do some analysis and determine speed benefits of DAX versus power query M language. I’m trying to do some calculations on numbers and would like to know what is more efficient as far as memory usage on the large database. Using either a calculated column in DAX or power query I am language writing a formula to do the calculations.
If you are creating a calculated column on imported data, it is almost always better to create the calculated column before reaching the Excel data model (Power Pivot window), whether it be in M query or in the original data source.
Hi Tom, thanks for the reply.
So your suggestion is to use either Dax or M?
Is one better than the other?
I’m trying to follow best practices. For creating new files. I need the most speed as our files are 100mb and can take 15-20 minutes to poll our local sql server (retail system)
thanks
What I am trying to say is that if you have a choice where to make a calculated column, it is most often better to create it before importing into the Excel data model (Power Pivot window).
Since your ultimate data source is a SQL Server, it would be considered a best practice to create the calculated column in a database view, before it ever reaches Power Query (M).
If you are not allowed to make the change in the actual SQL database, Power Query would be the next recommended step.
When you make a calculated column in the Power Pivot window, you are likely to loose the benefit of some data optimization provided by the DAX engine that is applied during import. Calculated columns are evaluated after that initial optimization occurs.
How many rows are in your 100 MB file(s)?
How many columns are in your fact table? Do you need all of them?
The forum ‘Power Pivot’ is closed to new topics and replies.