_edited_edited.png)
1. Introduction: What is DAX?
DAX (Data Analysis Expressions) is a collection of functions, operators, and constants that can be used in formulas or expressions to calculate and return one or more values. It enables us to create new information from data already in your model, providing powerful tools for custom calculations in Power BI. (Defnition from https://learn.microsoft.com/)
2. Why We Need DAX
DAX is essential for advanced calculations beyond simple aggregations like sums or averages. With DAX, we can:
Create calculated columns, tables, and measures directly in Power BI, reducing the need for external transformations.
Build custom aggregations and summaries, like running totals or moving averages, needed for in-depth analysis.
Drive visualisations with custom metrics, making dashboards more insightful.
Create interactive reports by enabling measures that respond dynamically to filters, slicers, and cross-highlighting.
Easily compare metrics across time periods, such as month-over-month growth, year-to-date totals, or year-over-year comparisons.
3. What DAX is Used For
DAX is primarily used to create:
Calculated Columns: These are column-level calculations applied to each row.
Calculated Measures: Measures are aggregations or calculations that respond to filter context dynamically.
Calculated Tables: These are entire tables created with DAX formulas.
4. How to Understand DAX
To build a strong foundation in DAX, focus on three core concepts:
Syntax: Defines the structure of DAX formulas.
Functions: Provide predefined formulas for calculations, data manipulation, and time-based analysis.
Context: Determines how data is evaluated, including row context (the current row in a calculation) and filter context (the filters applied in a calculation).
5. Syntax in DAX
Syntax includes the elements that make up a formula. For example, here’s a simple measure formula:
Total Sales = SUM(Sales[Sales Amount])
This formula has several syntax components:
Measure name: Total Sales.
Equals sign operator: =.
Function: SUM, which adds up all numbers in the specified column.
Table: Sales.
Column: [Sales Amount] within the Sales table.
We can read this formula as,
For the measure named Total Sales, calculate (=) the SUM of values in the [SalesAmount ] column in the Sales table.
6. Functions in DAX
A DAX function is a predefined formula used for calculations, transformations, and data manipulation. DAX offers various function types:
Aggregation Functions: Perform calculations across rows, such as SUM, AVERAGE, MIN, and MAX.
Time Intelligence Functions: Handle time-based calculations, like TOTALYTD, CALENDAR, and CALENDARAUTO.
Filter Functions: Control data inclusion, such as FILTER, ALL, and CALCULATE.
Logical Functions: Return true/false results, such as IF, AND, OR, and SWITCH.
Math and Statistical Functions: Perform math and statistical calculations, such as ROUND, DIVIDE.
Example of Using a Function
To calculate total revenue for sales in the current year:
Current Year Revenue = CALCULATE(SUM(Sales[Sales Amount]), YEAR(Sales[Date]) = YEAR(TODAY()))
In this example:
SUM aggregates Sales Amount.
CALCULATE applies a filter to restrict the data to rows where the Date column matches the current year.
Thus each function type in DAX serves specific purposes, giving you flexibility in designing calculations to meet your analytical needs.
7. Context in DAX
Context is crucial in DAX, enabling calculations to respond dynamically to filters and selections. There are two types of context in DAX: row context and filter context.
Row Context
Row context refers to the current row in a table when evaluating a formula. It applies automatically in calculated columns and iterators.
Example of Row Context in the calculated column:
Suppose you have a table called Sales with columns for Quantity and Unit Price. To create a calculated column in Sales Table,
TotalAmount = Sales[Quantity] * Sales[Unit Price]
Here, DAX evaluates each row in the Sales table individually, multiplying Quantity by Unit Price.
Filter Context
Filter context is the set of filters applied to data before an expression is evaluated.It defines which rows in the data model are considered during calculations. It enables dynamic calculations based on filters, slicers, and visuals in Power BI.
Example of Filter Context in measure:
Imagine we have a Sales table with columns for Date, Region, and Sales Amount. To create a measure that calculates total sales only for a specific region (e.g., “North”), we could write:
Total Sales for North = CALCULATE(SUM(Sales[Sales Amount]), Sales[Region] = “North”)
This measure applies a filter to only include rows where Region = “North” before summing Sales Amount.
In summary, filter context is essential for dynamic calculations in DAX, allowing you to tailor your analysis to specific slices of data by controlling which rows are included in each calculation.
8. Difference Between Calculated Columns and Measures
In DAX, calculated columns and measures are both used to create new calculations and insights, but they serve different purposes and behave differently in Power BI.
Calculated columns perform row-level calculations, while measures create aggregations that adjust dynamically based on the report’s filters and slicers.
Here’s a breakdown of the differences between the two:
Difference Between Calculated Column and Calculated Measure
Conclusion
Whether it’s for creating calculated columns, custom measures, or complex aggregations, DAX enhances the flexibility and interactivity of reports, making it a vital skill for advanced Power BI users. Embracing DAX allows you to maximise the potential of your data, transforming raw numbers into actionable insights that drive better decision-making.