Conditional column

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.