DAX Formulas and Measures in Power BI

DAX (Data Analysis Expressions) is a formula language introduced by Microsoft for Power BI, Analysis Services, and Power Pivot in Excel. It is designed to create custom calculations on tabular data models. Unlike Excel formulas that work on a cell-by-cell basis, DAX works on tables and columns, enabling powerful aggregations and dynamic calculations. The primary reason for DAX's existence is to allow business users to define complex business logic without writing traditional programming code. In the context of DP-900, you need to understand that DAX is used to create measures and calculated columns, but the exam focuses heavily on measures.

DAX operates on an in-memory columnar database engine called VertiPaq. When you write a DAX formula, it is compiled into a query plan that VertiPaq executes. The engine stores data compressed in columns, and DAX formulas leverage this structure for fast aggregations. A key concept is the filter context. When a measure is evaluated, the current filter context determines which rows from the underlying tables are included. For example, if a slicer filters by year 2023, the measure's SUM will only sum values from rows where the year is 2023. The filter context is automatically propagated through relationships. Another crucial concept is row context, which exists in calculated columns and iterator functions (like SUMX). In row context, the formula is evaluated for each row individually.

Aggregation Functions: - SUM(<column>): Adds all values in a column. Only works with numeric columns. - COUNT(<column>): Counts the number of non-blank values in a column. - COUNTROWS(<table>): Counts the number of rows in a table. - DISTINCTCOUNT(<column>): Counts the number of distinct values in a column. - AVERAGE(<column>): Calculates the arithmetic mean of values in a column. - MIN(<column>), MAX(<column>): Returns the smallest or largest value.

Logical Functions: - IF(<logical_test>, <value_if_true>, <value_if_false>): Returns one of two values based on a condition. - SWITCH(<expression>, <value1>, <result1>, ..., <else>): Evaluates an expression against multiple values.

Filter Functions: - CALCULATE(<expression>, <filter1>, <filter2>, ...): Changes the filter context for the evaluation of an expression. This is the most important and powerful DAX function. It allows you to apply custom filters on top of the existing filter context. - FILTER(<table>, <filter_expression>): Returns a table that is a subset of the original table based on a filter condition. Often used inside CALCULATE. - ALL(<table>) or ALL(<column>): Removes all filters from the specified table or column. Used to create grand totals or override filter context. - ALLSELECTED(<table>): Removes filters from the specified table but retains filters from slicers and other external filters. - VALUES(<column>): Returns a one-column table of distinct values from a column, respecting the current filter context.

Time Intelligence Functions: - TOTALYTD(<expression>, <dates>): Calculates the year-to-date value. - SAMEPERIODLASTYEAR(<dates>): Returns a set of dates from the previous year. - DATEADD(<dates>, <number_of_intervals>, <interval>): Shifts a set of dates by a specified number of intervals.

When a measure is evaluated, Power BI automatically follows relationships between tables to propagate filters. For example, if you have a Sales table and a Date table linked by a date key, a slicer on Date[Year] will filter the Sales table through the relationship. Measures like SUM(Sales[Amount]) will only sum rows that match the selected year. This is called automatic cross-filtering. If you need to override this behavior, you use CALCULATE with filter functions or use the USERELATIONSHIP function to activate an inactive relationship.

1. In Power BI Desktop, go to the Report view. 2. Right-click on a table in the Fields pane and select 'New measure'. 3. Enter the DAX formula in the formula bar. For example:

Total Sales = SUM(Sales[Amount])

Running Total:

Running Total = 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALL('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

This uses CALCULATE to sum sales for all dates up to the current date in the filter context.

Percentage of Grand Total:

% of Total Sales = 
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(
        SUM(Sales[Amount]),
        ALL(Sales)
    )
)

The ALL function removes filters from the Sales table, giving the total sales regardless of any slicers. DIVIDE handles division by zero gracefully.

Dynamic Grouping:

Sales Category = 
SWITCH(
    TRUE(),
    [Total Sales] < 1000, "Low",
    [Total Sales] < 5000, "Medium",
    "High"
)

This measure returns a category based on the total sales value in the current filter context.

When you drop a measure into a visual, Power BI automatically creates the appropriate filter context based on the visual's structure. For example, a bar chart with Category on the axis and a measure as the value will evaluate the measure for each category. The measure sees only the rows belonging to that category. If you add a slicer, it further restricts the filter context. Understanding this interaction is key to designing correct measures.

Sales per Sq Ft = DIVIDE(SUM(Sales[Amount]), SUM(Stores[SquareFeet]))

This measure works because the filter context from the visual (e.g., store name) automatically filters both the Sales and Stores tables through a relationship. However, a common misconfiguration is forgetting to establish a relationship between Sales and Stores on the StoreID column. Without it, the measure returns incorrect results because the filter does not propagate. In production, this dashboard is used by regional managers who slice by date and store. The measure must be efficient; using DIVIDE avoids division by zero errors. Performance is critical because the Sales table has millions of rows. The solution uses a star schema with a Date dimension, Store dimension, and Sales fact table.

Revenue YoY % = 
VAR CurrentRevenue = SUM(Revenue[Amount])
VAR PreviousRevenue = CALCULATE(SUM(Revenue[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE(CurrentRevenue - PreviousRevenue, PreviousRevenue)

This uses SAMEPERIODLASTYEAR which requires a properly marked date table. A common mistake is using a date column that is not contiguous or missing dates, causing the function to return incorrect results. In production, the date table must have a continuous range of dates with no gaps. The measure is used in a matrix visual with months on rows and years on columns. The filter context from the visual ensures that SAMEPERIODLASTYEAR returns the correct previous period. Misconfiguration often leads to blank values for months without prior year data.

Customer Tier = 
SWITCH(
    TRUE(),
    [Total Sales] > 10000, "Gold",
    [Total Sales] > 5000, "Silver",
    "Bronze"
)

This measure is used in a visual that lists customers and their tier. However, because it is a measure, it is evaluated in the filter context of each customer row. If the visual includes a slicer for date, the tier changes dynamically. A common error is using a calculated column instead, which would freeze the tier at refresh time. In production, this measure is combined with a disconnected table to allow users to select a tier and see only those customers. The measure must be carefully tested because if the visual does not include a customer-level grain, the measure might aggregate incorrectly.