Forum Discussion
What's the difference between adding column in query, or after with DAX?
I have revenue and loss data imported into Power BI. I have two options for creating a new column for profit: First, create it as a calculated column in the query. Second, create a new column wi...
Hi Anonymous ,
This is a list of the most important differences between DAX calculated columns and Power Query computed columns considering specific scenarios.
- Adding a column to the model
- A new DAX calculated column does not require a full refresh of the table.
- A new Power Query computed column requires a full refresh of the table.
- Full refresh of the table
- The engine processes DAX calculated columns after the data of all the partitions has been loaded and compressed in memory.
- A Power Query computed column is processed like any other column coming from the data source.
- Incremental refresh and partition refresh
- The engine processes the DAX calculated columns for all the rows of the table, even when only a few rows are added to or refreshed in the table.
- The engine only computes values for the Power Query computed columns of the rows that are processed in any partial refresh.
- Comparing compression
- A DAX calculated column does not participate in the evaluation of the best sort order for the compression. As a result, the compression of the column might be far from ideal, especially for columns with a low number of unique values.
- A Power Query computed column is compressed like any other column of the table.
- Processing time
- The cost of processing a single DAX calculated column corresponds to a sequential evaluation of the DAX expression for each row included in a refresh operation. Multiple DAX calculated columns within the same table are processed sequentially, one after the other, with an order compatible with calculation dependencies.
- The cost for processing a Power Query calculated column mostly depends on the data source when the expression is optimized using query folding. For example, if M expressions are translated into equivalent SQL expressions, performance and parallelism entirely depend on the SQL data source.
- Improving the compression of one column might negatively impact the compression of other columns in the data model.
- Query performance
- A column with better compression is smaller in memory and usually provides better performance levels. This is important in filter, group, and aggregation operations involving the column.
- The compression of a DAX calculated column might be lower than that of a Power Query computed column.
- An improvement in the compression of one column might negatively affect the compression of other columns in the data model.
You can also refer these articles and videos which introduce the difference in details, hope it could help:
- Comparing dax calculated columns with power query computed columns
- Power BI: M vs. DAX and Measures vs. Calculated Columns
- The VertiPaq Engine in DAX
- Dax query engine internals
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- Adding a column to the model
v-yingjl
Community Support
Community SupportHi Anonymous ,
This is a list of the most important differences between DAX calculated columns and Power Query computed columns considering specific scenarios.
- Adding a column to the model
- A new DAX calculated column does not require a full refresh of the table.
- A new Power Query computed column requires a full refresh of the table.
- Full refresh of the table
- The engine processes DAX calculated columns after the data of all the partitions has been loaded and compressed in memory.
- A Power Query computed column is processed like any other column coming from the data source.
- Incremental refresh and partition refresh
- The engine processes the DAX calculated columns for all the rows of the table, even when only a few rows are added to or refreshed in the table.
- The engine only computes values for the Power Query computed columns of the rows that are processed in any partial refresh.
- Comparing compression
- A DAX calculated column does not participate in the evaluation of the best sort order for the compression. As a result, the compression of the column might be far from ideal, especially for columns with a low number of unique values.
- A Power Query computed column is compressed like any other column of the table.
- Processing time
- The cost of processing a single DAX calculated column corresponds to a sequential evaluation of the DAX expression for each row included in a refresh operation. Multiple DAX calculated columns within the same table are processed sequentially, one after the other, with an order compatible with calculation dependencies.
- The cost for processing a Power Query calculated column mostly depends on the data source when the expression is optimized using query folding. For example, if M expressions are translated into equivalent SQL expressions, performance and parallelism entirely depend on the SQL data source.
- Improving the compression of one column might negatively impact the compression of other columns in the data model.
- Query performance
- A column with better compression is smaller in memory and usually provides better performance levels. This is important in filter, group, and aggregation operations involving the column.
- The compression of a DAX calculated column might be lower than that of a Power Query computed column.
- An improvement in the compression of one column might negatively affect the compression of other columns in the data model.
You can also refer these articles and videos which introduce the difference in details, hope it could help:
- Comparing dax calculated columns with power query computed columns
- Power BI: M vs. DAX and Measures vs. Calculated Columns
- The VertiPaq Engine in DAX
- Dax query engine internals
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.