DAX (Data Analysis Expressions) Overview

Introduction to DAX

• DAX allows custom calculations and measures in Power BI.
• Essential for Power BI developers and data analysts.

Main Categories of DAX Functions

• Arithmetic Operators
• Comparison Operators
• Text Operators
• Logical Operators

Usage of DAX Functions

• Calculated Columns: Perform row-level calculations.
  • Example: Calculating revenue from price and sales columns.
  • Methods: Data view, Report view, Table field selection.
• Calculated Measures: Perform aggregated level calculations without storing values.
  • Example: Calculating total revenue with discounts.
  • Used in report view for visualizations.

Implicit vs. Explicit Measures

• Implicit Measures: Automatically created when a column is dragged into the report.
• Explicit Measures: Custom measures created using DAX; more flexible and can handle complex logic.

Creating Tables Using DAX

• Functions: calendar, calendarAuto
  • calendar: Create a date table with a specific start and end date.
  • calendarAuto: Automatically create a date table covering full years of existing dates in the model.

Date and Time Functions

• Functions: date, dateDiff, dateValue
  • date: Converts year, month, day to date format.
  • dateDiff: Calculates difference between two dates in specified intervals (e.g., days, hours).
  • dateValue: Converts text date to date format.
• Dynamic Date Functions: now, today
  • now: Current date and time.
  • today: Current date.
• Extract Year, Month, Day: Functions for extracting parts of dates.
• Advanced Date Functions: eDate, eomonth, networkdays, weekday
  • eDate: Adds/subtracts months from a date.
  • eomonth: Returns last day of the month for a given date.
  • networkdays: Calculates working days between dates, excluding weekends and holidays.
  • weekday: Returns day number of the week for a date.

Aggregate Functions

• Basic Functions: sum, count, average, counta
• Expression Aggregation Functions: sumx, countx, averagex, etc.
  • Calculate expressions for each row then aggregate results.
• Distinct Functions: distinctCount, distinct
  • distinctCountNoBlank: Counts distinct values excluding blanks.
  • distinct: Returns table of unique values.

Logical Functions

• Functions: if, ifError, and, or, switch
  • if: Returns different values based on a logical test.
  • ifError: Returns value if there's an error; otherwise returns result.
  • and, or: Multiple logical conditions.
  • switch: Simplifies multiple if conditions.

Text Functions

• Functions: upper, lower, concatenate, len, left, right, mid, replace, substitute, search
  • String manipulation and extraction functions.
• Capabilities: Change case, concatenate strings, calculate length, extract parts of text, replace or substitute text, search within text.

Important DAX

• Calculate: Evaluates an expression with specified filters.
  • Usage: Advanced calculations with multiple filter contexts.
• Related: Uses relationships in the model to fetch values from related tables.
• RelatedTable: Returns a table from a related table, unlike related which returns a scalar value.
• CrossFilter: Changes the direction of filter propagation between related tables.