What's more efficient? Creating columns via add columns and dax w/in the script or adding columns?

[Anonymous]

What's the best way to keep your model fast and memory efficient?  If i need to add a column is it best via add columns and dax in the script or adding columns manually and adding dax in the added column.

Hope that makes sense.

[v-alq-msft]

Hi, @Anonymous 

I'm sorry for the misunderstanding. I'd like to suggest you use Performance Analyzer. In Power BI Desktop you can find out how each of your report elements, such as visuals and DAX formulas, are performing. Using the Performance Analyzer, you can see and record logs that measure how each of your report elements performs when users interact with them, and which aspects of their performance are most (or least) resource intensive.

For further information, you may refer to the article .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft]

Hi, @Anonymous 

With calculated columns, you can add new data to a table already in your model. But instead of querying and loading values into your new column from a data source, you create a Data Analysis Expressions (DAX) formula that defines the column’s values. In Power BI Desktop, calculated columns are created by using the new column feature in Report view. While with Query Editor, you create and rename your custom column to create PowerQuery M formula queries to define your custom column.

Unlike custom columns that are created as part of a query by using Add Custom Column in Query Editor, calculated columns that are created in Report view or Data view are based on data you’ve already loaded into the model.

For further information, you may refer to the following links.

https://community.powerbi.com/t5/Desktop/Performance-Impact-with-Calculated-Columns/td-p/115288

https://radacad.com/m-or-dax-that-is-the-question

https://docs.microsoft.com/en-us/power-bi/desktop-calculated-columns

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I think you are all misunderstanding my question, it's not whether M query is faster its whether method 1 or method 2 below is faster:

1. Create a calculated table and use the ADD tables dax command to add a column

OR

2.  Create a table and hit the add column button and do the calculation in the newly added column

Just wanted to see if one or the other was more efficient.

[amitchandak]

While in this case Edit Query, the custom column would be faster. But refer to this

https://radacad.com/m-or-dax-that-is-the-question

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[BobBI]

Hi ,

Sometimes it depend on your data model and requirement , but in general if you add a column in power query and populate value using M then it will get executed while loading/refreshing query.

If you add column using DAX expresion , then it would populate values after data load was completed , in my undersing this appreach would work faster as it use already loaded data and give less burdon while loading/refreshing data.

thanks,

Sukhi

[VasTg]

@Anonymous 

Are you comparing adding a new column in query editor(M Query - Add column tab) vs calculated column(DAX - Data View)?

I would say query editor because it executes at the early stage but defer to experts.

If it helps ,mark it as a solution

Kudos are nice too.