What is DAX?

Data Analysis Expressions (DAX) is a powerful formula language used in Power BI, Excel Power Pivot, and SQL Server Analysis Services. It is designed to work with data models and enables you to create calculations, aggregations, and custom measures to analyze data that is already loaded in your model.

While similar to Excel formulas, it offers greater power and flexibility, making it ideal for complex data modeling and business intelligence tasks.

DAX allows you to create

• Create calculated columns and new measures.
• Build complex aggregations and calculations.
• Manipulate data at various levels of granularity.

Key concepts

1. Calculated columns

• The calculated column will be added to the data model and computed row by row just like any other column.
  Example –
  NewColumn = [Column1 * 12] + [column2]

2. Measures

• By using measures, you can perform calculations on your data while interacting with your reports.
  Example –
  NewColumn = SUM(sales[column1] – sales[column2])

3. Functions

• By using DAX pre-defined operation, various calculations and manipulations of data can be performed. Few aggregate functions are – SUM(), COUNT(), FILTER(), ALL(), IF(), AND(), OR(), CONCATENATE(), UPPER() etc.
  Example:
  TotalSales = SUM(Sales[Revenue])

4. Tables and Relationships

• DAX works with tables and takes into account the relationships between them within a data model.
• Functions like RELATED and RELATEDTABLe can be used to create relationships between tables.

5. Variables:

• DAX variables help break down complex calculations into manageable steps or store intermediate results. Variables improve performance and readability.
  Example:
  var commission= DIVIDE( policies[Commission_percentage] * [TotalPremium], 100 )
  Return commission