What's the difference between adding column in query, or after with DAX?

[Anonymous]

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 with DAX

My question: is there a performance difference, or any advice in which is best practice? 

[v-yingjl · Accepted solution]

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:

1. Comparing dax calculated columns with power query computed columns 
2. Power BI: M vs. DAX and Measures vs. Calculated Columns 
3. The VertiPaq Engine in DAX 
4. 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.

[jdbuchanan71]

If you do it in M and store it as a column the model, assuming it will have a high cardinality, it will take up a lot of room in memory for no benefit.  The Vertipaq engine is designed to do the calculations in my example very quickly even on large data sets.

[jdbuchanan71]

@Anonymous 

If your Profit is simply Revenue - Loss then your best option is to do it with measures.

Revenue Measure = SUM ( YourTable[Revenue] )

Loss Measure = SUM ( YourTable[Loss] )

Profit Measure = [Revenue Measure] - [Loss Measure]

[Anonymous]

If working with big data, is there any difference in the performance of doing it in the query with M language versus DAX? Curious to learn more about how Power BI works