1. Simplifying measure formulas with variables
1.1. This is a powerful technique for making complex formulas even easier to understand
1.1.1. We declare variables using the VAR keyword and variable names cannot contain spaces
1.1.2. Variables can hold literal values and they can also hold entire DAX functions
1.1.3. After your variables comes the keyword RETURN and then this is followed by your actual measure expression, which uses variables to provide intuitive substitutions
1.1.4. As an example, we can apply variables to some multi-line measure expression
1.1.4.1. Before
1.1.4.2. After
2. Adding comments to formulas
2.1. Comments can be added anywhere in your DAX formulas by prefixing with a double forward slash, e.g. // My comment
3. Calculated columns vs measures
3.1. You can add columns to tables and utilise DAX functions
3.1.1. These can be added by clicking the New Column button and adding an expression in the form: Column name = {expression}
3.1.1.1. The {expression} doesn't have to include a DAX function, it can just be a simple calculation involving a couple of columns in that table
3.2. Although it is tempting to add a calculated column such as the following to a Sales fact table, you should instead look to do this as a measure (because it will perform more efficiently in the report
3.2.1. Costs = Sales[Order Quantity] * Sales[Total Unit Cost]
3.2.2. Calculated columns take up memory space. They produce calculated values for every row in your table so adding calculated columns to large fact tables will quickly bloat the size of that table in memory. In contrast, measures take up no memory and calculate very rapidly in your reports.
3.3. Where calcaulted columns make a lot more sense is dimension/lookup tables (i.e. tables we use for the report filters)
3.3.1. As an example, we can add a Product Group calculated column that sets a value depending on a measure value (per Product Name in this case), where the measure is named [Total Sales]
3.3.1.1. Product Group = SWITCH( TRUE(), [Total Sales] > 10000000, "Top", [Total Sales] <= 10000000, "Bottom" )
3.3.1.2. Screenshot
4. Understanding DAX calculation context
4.1. It is important to always think about the context that applies to every calculated value in your report visualisations.
4.1.1. There are multiple things that affect context for a DAX measure
4.1.1.1. Filtering attributes in the same visual as the DAX measure
4.1.1.2. Slicer selections, which apply to all visuals on the same page
4.1.1.3. Clicking on any part of any visual can also act like a slicer
4.1.2. For example, consider the example screenshot
4.1.2.1. Screenshot
4.1.2.1.1. Thinking about the highlighted "total sales" value of 25,393.00...
4.2. Types of context
4.2.1. Evaluation context
4.2.1.1. Your DAX formulas only start their calculation work after the filters have applied
4.2.1.2. The combo of all filters effectively reduces the dataset, and understanding evaluation context means understanding the reduced dataset that a specific calculation starts with
4.2.1.3. Non-calculated attributes in the same visual as the DAX calculation can be thought of providing many little datasets for individual calculations to be done
4.2.1.3.1. Slicer selections and selections on other visual components provide additional filtering
4.2.2. Aggregation context
4.2.2.1. You will commonly hear that these functions operate within filter context
4.2.2.1.1. But it's better to think of function invoking the aggregation engine and understanding how that works
4.2.2.2. There are two calculation engines in Power BI: aggregation engine and iteration engine
4.2.2.2.1. DAX functions fall into one of these two categories
4.2.2.3. Aggregation functions are simpler and act on an entire numeric column in its evaluation context (reduced dataset after all filters applied)
4.2.2.3.1. Examples of aggregation functions include SUM, AVERAGE, MIN, MAX, COUNT, etc.
4.2.3. Iteration context
4.2.3.1. Rather than acting on the entire column within the evaluation context, iterating functions calculate some expression for every row, saving those to memory and then perform some associated aggregation function on those in-memory values
4.2.3.2. Iterating functions are identifiable by finishing with an X
4.2.3.2.1. For example, SUMX, AVERAGEX, MINX, etc.
4.2.3.3. In almost all scenarios, iterating functions are preferable to adding a calculated column and then using an aggregation function on that calculated column
4.2.3.3.1. This is because they work after evaluation context is set and therefore take up much less space in memory than calculated columns
4.2.3.4. You will commonly hear that these functions operate within row context
4.2.3.4.1. But it's better to think of function invoking the iteration engine and understanding how that works
4.3. Context transition
4.3.1. When you create a calculated column, the calculation happens in row context
4.3.1.1. This is intuitive when you use one or more columns in the same table to perform the calculation
4.3.1.2. However, when you use a DAX function such as SUM (an aggregation function) the row context does not work
4.3.1.2.1. Essentially, these DAX functions do not work with filters when applied to a calculated column
4.3.1.2.2. When the calculation happens in row context, it ignores the filter context, so you end up with an aggregated value for the entire column repeating for every row
4.3.1.3. Side note: avoid calculated columns as much as possible, especially in large fact tables
4.3.1.3.1. Measures are always preferable because they do not bloat out the memory footprint of the data model
4.3.2. Use of the CALCULATE DAX function as a wrapper makes the calculations respect its in-row context by performing context transition from row context to filter context
4.3.2.1. Although this seems slightly counter intuitive at first glance, it makes sense when you remember that the other column values in the table act as filters for the DAX calculated values (in the calculated column) when the context is filter context
4.3.2.2. We can simply wrap a DAX function like SUM in CALCULATE without any need to specify an optional filter argument and this will achieve the row context to filter context transition
4.3.2.3. However, we should remember that when we use a measure in a calculated column, there is no need to wrap that in CALCULATE because this happens automatically (i.e. implicitly) behind the scenes
4.3.3. Example: adding total sales (revenue) per customer to the customer table, where the revenue comes from the sales table and there is a 1-to-many relationship in the data model from customer to sales
4.3.3.1. Calculated column in row context
4.3.3.2. We add CALCULATE as a wrapper in the calculated column, and the switch to filter context gives us the expected results per customer
4.3.3.3. However, we don't need to create calculated columns using CALCULATE; it is better to use measures
4.3.3.3.1. Here we have a Total Sales measure that is the same as our original calculated column formula
4.3.3.3.2. When we use the measure there is no need to explicitly use CALCULATE; the DAX calculation engine automatically applies CALCULATE to measures added to calculated columns
5. Data types
5.1. Pay attention to the data types in Query Editor
5.1.1. Most of the columns we're going to want for DAX formulas will be numeric types
5.1.2. If any column is typed as text instead of a number or date, for example, the DAX formula is not going to work correctly
6. Naming conventions
6.1. Get table and column names defined in Query Editor
6.2. Avoid abbreviations
6.3. Use spacing between words
7. Formula syntax
7.1. New measures are defined by typing the name followed by equals followed by the DAX function
7.1.1. DAX functions generally accept one or two arguments that either reference a table, a column or an expression
7.1.1.1. Columns cannot be referenced on their own, they must always be prefixed by the table like this: 'Table Name'[Column Name]
7.1.1.2. Expressions can include table/column references, operators like *, /, +, - , other DAX functions or other measures (which encapsulate DAX functions)
8. Measure groups
8.1. It is widely considered best practice to organise all your model's measures into one or more measure groups and not to create them within your tables
8.1.1. Example: create a new "Total Sales" measure from the 'Sales'[Line Total] column and add to new group named "Key Measures"
8.1.1.1. 1. Create new measure initially in table where the main fact metric is you want to base your measure on
8.1.1.1.1. DAX is:
8.1.1.2. 2. Create a new measure group via the following trick:
8.1.1.2.1. Create a new table via report editor Home | Enter Data
8.1.1.2.2. Move the "Total Sales" measure to the new "Key Measures" table
8.1.1.2.3. Delete the redundant Column1 from the "Key Measures" table
8.1.1.3. 3. Layout the new measure groups at the bottom of the model (in model view)
8.1.1.3.1. Note that measure group tables do not get related to regular model tables via relationships because they are based on regular table attributes (so the relationships are implicit)
8.1.1.3.2. The idea is no to let these special tables rest in their default position in the model, but to line them up at the bottom as a way of keeping them organised and visually obvious in their identity as measure groups
9. Formatting measure expressions
9.1. Use line breaks (Shift+Enter) and indentation (tab or spaces) to create a logical, more readable structure for the measure definition
9.1.1. Example