Home Forums Power Pivot Power Query vs Dax for calculations

Tagged: ,

This topic contains 3 replies, has 2 voices, and was last updated by  tomallan 1 year, 8 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #18071

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    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.

    #18073

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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.

     

    #18074

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    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

    #18075

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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?

Viewing 4 posts - 1 through 4 (of 4 total)

The forum ‘Power Pivot’ is closed to new topics and replies.