Introduction to DAX – Calculations and Aggregations - Textnotes

Introduction to DAX – Calculations and Aggregations

This module introduces DAX (Data Analysis Expressions), Power BI’s formula language. Learners will understand the difference between calculated columns and measures, use aggregation and count functions, and apply time intelligence functions for reporting. Hands-on exercises reinforce building totals, averages, and year-to-date metrics.

1. Calculated Columns vs Measures

  1. Calculated Column: Added to a table, calculated row by row. Stored in the data model.
  2. Measure: Calculated dynamically based on filter context. Not stored physically in the table.

Example:

  1. Calculated Column: Profit = SalesAmount - Cost → Exists for each row
  2. Measure: Total Sales = SUM(Sales[SalesAmount]) → Calculates total dynamically based on filters

2. Aggregation Functions: SUM, AVERAGE, MIN, MAX

  1. SUM: Adds all values in a column
  2. AVERAGE: Calculates the mean of a column
  3. MIN/MAX: Finds smallest/largest value

Example:

  1. Total Sales = SUM(Sales[SalesAmount])
  2. Average Sales = AVERAGE(Sales[SalesAmount])
  3. Highest Sale = MAX(Sales[SalesAmount])

3. Count Functions: COUNTROWS, DISTINCTCOUNT

  1. COUNTROWS: Counts the number of rows in a table or filtered table
  2. DISTINCTCOUNT: Counts unique values in a column

Example:

  1. Order Count = COUNTROWS(Orders)
  2. Unique Customers = DISTINCTCOUNT(Orders[CustomerID])

4. Time Intelligence Functions: YTD, QTD, MTD, PREVIOUSYEAR, SAMEPERIODLASTYEAR

Time intelligence functions allow analysis across time periods.

  1. YTD (Year-to-Date): Sum of values from the start of the year until a selected date
  2. QTD (Quarter-to-Date): Sum from the start of the quarter
  3. MTD (Month-to-Date): Sum from the start of the month
  4. PREVIOUSYEAR: Compare metrics with the previous year
  5. SAMEPERIODLASTYEAR: Same period in the previous year

Example:

  1. Sales YTD = TOTALYTD(SUM(Sales[SalesAmount]), Dates[Date])
  2. Sales Last Year = CALCULATE(SUM(Sales[SalesAmount]), SAMEPERIODLASTYEAR(Dates[Date]))