Fundamental concepts of DAX in Power BI

Data Analysis Expressions (DAX) is a powerful formula language used in Power BI, Excel, and other Microsoft tools to create custom calculations, perform data analysis, and build advanced business logic. Understanding the fundamental concepts of DAX is essential for effectively working with Power BI.

1. DAX Basics

• What is DAX?
  • DAX is a functional language designed to work with tabular data models.
  • It allows you to create calculated columns, measures, and tables.
• Syntax :
  • DAX formulas follow a consistent structure: FunctionName(Argument1, Argument2, ...).
  • Example: SUM(Sales[Amount]).

2. Calculated Columns vs. Measures

• Calculated Columns :

  • A calculated column is a new column added to a table in your data model.
  • The calculation is performed for each row in the table.
  • Example:

     Profit = Sales[Revenue] - Sales[Cost]

    Use cases: Adding static values or calculations that don’t depend on filters.

• Measures :

  • Measures are dynamic calculations that aggregate data based on context (e.g., filters, slicers, or visuals).
  • Measures are recalculated dynamically when the context changes.
  • Example:

    Total Sales = SUM(Sales[Amount])

    Use cases: Aggregations like totals, averages, percentages, etc.

3. Context in DAX

Context is one of the most important concepts in DAX. It determines how calculations are evaluated based on filters and relationships.

• Row Context :

  • Refers to calculations performed at the row level.
  • Automatically applies when creating calculated columns or using iterator functions (e.g., SUMX, AVERAGEX).
  • Example: 

    Profit = Sales[Revenue] - Sales[Cost]

• Filter Context :

  • Refers to calculations influenced by filters applied in visuals, slicers, or relationships.
  • Automatically applies when creating measures.
  • Example:

    Total Sales = SUM(Sales[Amount])

    This measure aggregates sales based on the current filter context (e.g., year, region).

• Understanding Relationships :

  • Relationships between tables affect how data flows and how filters propagate.
  • Use functions like RELATED (to fetch values from related tables) and CALCULATE (to modify filter context).

Here are the concepts that you can start with :
Context: Understanding context is fundamental in DAX. Filter context determines which data subset your formula works with, while row context refers to the current row during calculations.

Tables and Columns: DAX operates on tables and columns, not individual cells. This structure facilitates complex data analysis across related tables.

Measures and Calculated Columns:
- Calculated Columns are computed during data refresh and store values for each row in a table. They are useful for row-by-row calculations.
- Measures are dynamic calculations that respond to user interactions in reports, recalculating as filters and slicers change. They do not store data, making them more efficient for aggregations.

Functions: DAX includes a wide range of functions categorized into:
- Logical Functions (IF, AND, OR) for conditional operations.
- Mathematical and Statistical Functions (SUM, AVERAGE, COUNT) for aggregations and calculations.
- Text Functions (CONCATENATE, LEFT, RIGHT) for string manipulations.
- Date and Time Functions (DATE, CALENDAR, YEAR) for time-based calculations.
- Time Intelligence Functions (SAMEPERIODLASTYEAR, TOTALYTD) for analyzing data across different time periods.

Relationships: DAX leverages relationships between tables to perform calculations across different data sets, enabling complex data models and analyses.

CALCULATE : One of the most powerful functions in DAX, CALCULATE evaluates an expression in a modified filter context, allowing for complex filtering and context modifications.

Links to help you :

https://www.spguides.com/power-bi-dax

What Is DAX - Power Bi ? ??Complete Introduction?? - YouTube