Skip to main content

Module 4: DAX Basics in Power BI

Getting Started with DAX in Power BI

DAX (Data Analysis Expressions) is a formula language used in Power BI, Power Pivot, and Analysis Services to define custom calculations and queries on data models. DAX is designed to work with relational data and perform dynamic aggregation and filtering.

Measures vs calculated columns:

  • Measures are calculations performed on the fly based on user interactions and filters applied in reports. They are not stored in the data model but are computed when needed.

  • Calculated columns are added to tables in the data model and are computed during data refresh. They are stored in the model and can be used like any other column in the table.

  • Functions

  • Operators

  • Values Example: Total Sales = SUM(Sales[SalesAmount])

  • Calculated Columns

  • Calculated Tables

  • Quick Measures

  • Expressions

How to create Measures for use in visualizations

  1. In Power BI Desktop, open the report where you want to create a measure.
  2. In the Fields pane, right-click on the table where you want to add the measure and select New measure.
  3. In the formula bar, enter the DAX expression for the measure. For example, to create a measure that calculates total sales, you can use the following DAX formula: Total Sales = SUM(Sales[SalesAmount])
  4. Press Enter to create the measure. The new measure will appear in the Fields pane under the selected table.
  5. You can now use the measure in your visualizations by dragging and dropping it onto the report canvas.

Dax Reference

Microsoft Learn: https://learn.microsoft.com/en-us/dax/

Measures​

Dynamic Data

  • Evaluated at query time
  • Responds to filters and slicers
  • Not stored in memory

Calculated Columns​

Static Data

  • Computed at the row level within a table
  • Stored in the data model
  • Don't change based on context or filters

How to create Calculated Columns​

  1. In Power BI Desktop, open the report where you want to create a calculated column.
  2. In the Fields pane, right-click on the table where you want to add the calculated column and select New column.
  3. In the formula bar, enter the DAX expression for the calculated column. For example, to create a calculated column that concatenates first and last names, you can use the following DAX formula: Full Name = Customers[FirstName] & " "& Customers[LastName]
  4. Press Enter to create the calculated column. The new column will appear in the Fields pane under the selected table.
  5. You can now use the calculated column in your visualizations like any other column in the table. :)

Common DAX Functions​

  • SUM: Adds up all the values in a column.
  • AVERAGE: Calculates the average of values in a column.
  • COUNT: Counts the number of non-blank values in a column.
  • DISTINCTCOUNT: Counts the number of unique values in a column.
  • IF: Performs conditional logic.
  • CALCULATE: Modifies the context in which a measure is evaluated.
  • FILTER: Returns a table that represents a subset of another table.
  • RELATED: Retrieves related values from another table.
  • ALL: Removes filters from a table or column.
  • DIVIDE: Safely performs division and handles division by zero.