Comparing DAX calculated columns with Power Query computed columns

Learn how to choose between DAX and Power Query when a table needs to compute additional columns.

[EricaDyson]

Can't thank you enough for 1) your knowledge 2) the way you share your knowledge 3)the clarity. Your talent and humility are both amazing!

[Daervion]

Man, that was impressive from start to end.
I'm starting to get into Data Analysis and trying to get also on Data Science and this content is crystal clear.

Thanks for the content!

[bogkraven]

Just started the video and already know that it is going to be a good one! Your work is of astonishing value!

[nicemacey]

Another great video. My approach has always been to create calculated columns in Power Query so that they're available to use in PQ transformations, which isn't the case with DAX calculated columns.

[DJ_Q]

I literally almost spit my breakfast out when you said "That's the end of section 1" 😂
Great video as usual

[questionsformedb]

I will say obvious, they are the best DAX teachers on the planet who has significant positive influence on career of many people. Microsoft is very lucky to have them, they uncover the power of the tool.

[nburaq]

This is the best explanation I have ever seen so far! Clean, simple and satisfying.Thanks a lot

[beckhamrick]

Haha I loved section 1 Alberto. Grazie mille per tutti questi video. Ho imparato tantissimo da voi!

[philipmarno]

This was a great insight into DAX and model management. Have subscribed and will be back for more. Many thanks from Scotland.

[adrianomelo]

Incredible, thanks for your great collaboration, a big hug from Brazil

[maeshakib]

Very good tutorials. From your Very deep level discussion everyone will be benefited. Thank you Alberto Ferrari. Respect & ❤️ from Bangladesh.

[LasherSan]

This was a legitimately interesting video, with a clear explanation. Thanks.

[bkundrat]

helpful! Thank you Alberto. This answered my question in the exact detail I needed.

[jeffweir]

Great explanation. I guess an advantage of using PQ over DAX in this instance is that more of your 'like' logic (i.e. data modelling) is in one place.

[sue_bayes]

Thank you, enjoyed the reasoning behind using the different methods.

[felipesignorellireis]

Amazing You are "top" Alberto.

[alfredsfutterkiste]

Super interesting point about the partitions that I never considered. Im glad I watched this video

[scramiro]

Nice one, very funny. I laughed when you said "That's the end of section 1" and then kept staring hahaha. You fooled us hahaha Nice!

[questionsformedb]

I have 25M records view to pulls into SSAS on prim model from Oracle cloud database Unfortunately, I don't have ability to create materialized table from the view however I can modify the view on the server.. I had three options to create columns:
1) create additional columns in SQL view - it's probably best practice, however due to complex logic it's much easier to do it in Power Query.
2) create additional columns in Power Query - I created columns but solution started to fail. I suspect this happens because Power Query tries to use folding on top of already complex view on top of cross cloud data pull. it either timed out or Oracle database choked running the view due to not enough resources.
3) create additional columns in the model itself using DAX - I had no problem doing it even though they have to recalculate every time on update of single partition (there are 3 now).

In my case I used DAX calculated columns as a method of load distribution between Oracle Database with not enough resources to execute entire view and Analysis Services Database as the last step in creating complete view.

In other case, I had to create calculated column based on logic required two tables coming from different clouds. Power Query did join operations extremely slow with cross cloud data on 25 mln records while DAX engine even didn't blink an eye.

So the answer IT DEPENDS... :) there could be hundreds of reasons to determine one option over other. In general, my order is SQL, Power Query, DAX but it's not always possible for one reason or other and I have to deviate from general order.

[jimayas]

Grazie tanto!
Your videos are great and insightful as always