Slow Dataflow Refresh - Calculated vs Customs Columns

[ScottBrown]

Like others here, I am trying to consolidate multiple report datasets into one master data set ie a data warehouse file.

I am facing a calculated column (DAX) vs a custom column (Power Query) dilemma.

I have 43 transformations to do in one entity, doing transformations against an Item Number positioning matrix.

• If I do in DAX, the refresh is fast (Dataflow is clean with minimal filtering, doing at the dataset level in the report) - less than 5 mins to refresh.
• If I do it in Power Query in the dataflow, it takes almost 30 minutes to run

I read everywhere to keep it in Power Query, which would be nice to do.

Any suggestions or best practices based upon experience.

[v-luwang-msft · Accepted solution]

Hi @ScottBrown ,

About  Custom Column:

Before creating a new Custom Column in Power Query Editor, you should first be aware of the following:

• When you add a Custom Column in Power Query Editor, you have to use M language.
• Creating a new Custom Column or even performing bit changes on the currently created Custom Column requires more time depends on the table size because it needs a full refresh of the table.
• The Custom Column does compress, which makes the .pbix file smaller, and the performance usually faster.
• The size of the Custom Column in Power Query is always smaller than the created Calculated Column in DAX.
• You must have good knowledge of M language to can write a formula in a Custom Column in Power Query Editor.
• You can’t use DAX expressions in a Custom Column in Power Query Editor.

When to use Custom Column in Power Query?

• If you want to prepare your data and perform data transformation before the data loaded into the model in a more compact and optimal way, you should use Custom Column in Power Query.
• Use Custom Column, If your model is really huge.
• If your scenario requires adding multiple calculated or computed columns depends on other columns on the same row, consider using Custom Column.
• Use Custom Column, If you need to create a new column as a part of your query and you need to load values into your new column from a data source.

About Calculated Column:

Before creating a new Calculated Column in Power Query Editor, you should first be aware of the following:

• When you add a Calculated Column in Data View or Report View, you have to use DAX.
• In some cases, the DAX calculated column may increase the model storage and consume extra memory depends on the usage, the model size, the number of relationships, cardinality, and other aspects.
• Creating a new DAX Calculated Column is faster because it does not require a full refresh of the table, unlike the new Computed Column that requires a full refresh of the table.
• Unlike Custom Column, the DAX Calculated Column does not compress.
• The size of the DAX Calculated Column is always bigger than the created Custom Column in Power Query. (Actually, it doesn’t matter, the most important is the size of the entire model).
• It is not considered an optimum practice to use DAX for calculations if you can use Custom Column.
• Avoid using a Calculated Column if you want to use one of the iterator functions such as SUMX, COUNTX, MINX.
• You must have good knowledge of DAX to can write a formula in a Calculated Column in Report or Data View.
• You can’t use M language in a Calculated Column in Report or Data View in Power BI.

When to use DAX Calculated Column in Power Query?

• If you want to perform in-depth data analysis on the top of the Model, you should use DAX in a Calculated Column in Data View or Report View.
• Use the Calculated column, If you need to perform DAX analysis after the data is loaded to your model.
• If you need to aggregate rows from other tables, consider using a DAX Calculated Column.
• If your formula calculation is very complex to be done in a Custom Column, so it’s better to use DAX in a Calculated Column.
• Use the Calculated column, If you can’t use Custom Column.

To learn more details ,refer:

https://devoworx.net/custom-column-vs-calculated-column-power-bi/ 

Best Regards

Lucien

[viraj_arsekar]

Great explanation. I'm a bit confused about at what time they refresh. When I refresh the dataset (Refresh now in Power BI Desktop or services, or scheduled refresh), does it refresh the underlying data (along with custom column - Power Query) as well as the semantic model (calculated column - DAX), or just the data? 

I'm unsure of how and when the semantic model can be refreshed without refreshing the entire dataset.

Edit: I know now that the custom column is evaluated when the report is refreshed and queries are sent to the sources, calculated columns are evaluated once the data is loaded in the data model. Most of the time both these happen when data refresh happens I believe?

[dbeavon3]

@v-luwang-msft 

You say "DAX Calculated Column does not compress".  Do you have a source?  I have read that they do compress, but not as efficiently as a normal PQ column.