Day 10: DAX (Data Analysis Expressions) in Power BI

On Day 10, we explore DAX (Data Analysis Expressions), the powerful formula language in Power BI used for data cleaning, calculations, and creating new insights. With DAX, you can build measures, calculated columns, and calculated tables to meet complex business requirements.

1. Introduction to DAX

• DAX is used for cleaning, transforming, and performing calculations on existing data.

• With DAX, you can create:

  1. Measures / Quick Measures / Calculated Measures

  2. Columns / Quick Columns / Calculated Columns

  3. New Tables / Calculated Tables

Note: Power BI contains over 1,500 DAX functions for various purposes.

Basic Syntax:

Example:

2. Measures vs Columns

2.1 NEW Measure

• Typically numeric.

• Returns a single scalar value.

• Measures do not require memory storage.

• Created measures are visible only in Report View, not in Table View.

• Measures work in a filter context, meaning the result changes depending on filters applied in visuals.

Use Case Example:

• Calculating Total Sales, Average Revenue, or Year-to-Date Sales dynamically in reports.

2.2 NEW Column

• Can be text, numeric, or date.

• Returns a value for each row (non-scalar output).

• Requires memory storage.

• Columns are visible in Table View, Model View, and Report View.

• Columns work in a row context, meaning the calculation is applied individually to each row.

Use Case Example:

• Creating Customer Category, Age from Birthdate, or Profit per Product Row.

3. DAX Functions

DAX provides a wide range of functions to handle various data types and operations:

1. Aggregate Functions: SUM, AVG, MIN, MAX, COUNT, COUNTA, COUNTROWS, COUNTBLANKROWS, COUNTDISTINCT

2. Number Functions – ROUND, CEILING, FLOOR, etc.

3. Text Functions – CONCATENATE, LEFT, RIGHT, LEN, etc.

4. Date Functions – YEAR, MONTH, DAY, DATEADD, etc.

5. Time Intelligence Functions – TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, etc.

6. Information Functions – ISBLANK, ISNUMBER, ISTEXT, etc.

7. Filter Functions – FILTER, ALL, ALLEXCEPT, CALCULATE, etc.

4. Iteration Functions

Iteration functions perform calculations row by row and then aggregate the results:

• SUMX, AVGX, AVGAX

• MINX, MAXX

• COUNTX, COUNTAX

Example:

This multiplies quantity by unit price for each row and then sums the results.

DAX is a powerful tool for advanced calculations in Power BI. Understanding the difference between measures, columns, and iteration functions is crucial for building accurate and dynamic reports.