Power BI Order of Operations

[AllisonKennedy]

We all remember learning order of operations in school, whether it was Please Excuse My Dear Aunt Sally, or BEDMAS, the order of operations is vital in understanding mathematics and getting the desired result from your calculations. 

Power BI and Excel are no different. We must understand the order of operations when building our reports in order to achieve the desired results. 

Order of Operations

Power BI/Excel order of operations is basically as follows: 

1. Raw data source updates/changes 
2. Power Query operations (M code) 
3. DAX tables and columns 
4. Slicers 
5. DAX measures 

When you click Refresh, it will go back to the Raw data source and check for updates, apply the query changes from Power Query, then calculate that DAX tables and columns and THEN apply the slicer selections. This means that only DAX measures update based on slicer selections, not DAX columns.

Relevance to Real World

Many questions stem from not understanding the order in which Power BI (or Excel) apply the transformations to our data source. It works a bit like an onion, adding layer on top of layer. You cannot use the output of a layer that hasn't yet been applied. 

This problem is most commonly encountered when working with calculated COLUMNS in DAX. Calculated columns are essentially part of the Data Model, and can usually be done in Power Query (which is what I recommend when possible). They are calculated on data refresh, and DO NOT CHANGE based on report filters or slicer selections.

Calculated MEASURES in DAX calculate AFTER all report filters and slicer selections. This makes them more dynamic and able to be manipulated easily by the report viewer or end user. DAX measures are the key to powerful insights. 

Originally posted on ExcelwithAllison.com

[Anonymous]

Thank you for sharing this! I've been looking for this exact thing for a while. I do have a follow up question if that's okay: 

Is there any best practices on which language to use for different needs? E.g. Anything we'd do with Power Query (M Code) can also be done with DAX (Tables and Columns), so when should I use what?

If we are running scheduled data imports for example, would the M code be ran when we refresh the dataset then the DAX (tables and columns) would be ran when the report renders? Or do they both get ran on the refresh?

Thank you in advance!

[AllisonKennedy]

@Anonymous They both get run when the report refreshes, so your DAX code can impact refresh times in the Power BI service (I just learned this the hard way, lol). The more you can do in Power Query M, the more efficient and smaller your data model will be. So I try to do tables and columns in M code as much as possible (I make exceptions sometimes, for example if I would need to do a merge tables to get the calculation and I'm already relating them in the data model, I might choose DAX for the column instead, but not sure in reality if the M merge tables would be any less efficient - it probably depends on the dataset).