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:
- Raw data source updates/changes
- Power Query operations (M code)
- DAX tables and columns
- Slicers
- 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.
