Close this search box.

Understanding how DAX works

Picture of Carol Otero Gómez
Carol Otero Gómez
| 13 June, 2024

If your destination points to the world of data, you better prepare your radar because you will surely encounter a language of expressions that will accompany you on your journey.

If you have heard of DAX, measures, calculated columns, but these concepts still overwhelm you, I will break them down for you below.DAX is the acronym for Data Analysis Expressions, these expressions can be found in Power Pivot – Excel, Power BI, and Analysis Services.With DAX, you can perform complex calculations, create dynamic calculation formulas or more commonly known as measures, add columns to existing tables or in other words create calculated columns, as well as create calculated tables from the existing tables in our data model.

To familiarize ourselves with DAX, it is necessary to have an understanding (for now) of two main ideas:

1. DAX Works by Evaluating Different Contexts

They can affect the result of an expression. These include:

  • Row Context
  • Filter Context
  • Context Transition

How to differentiate between one and the other?

First, we must understand that context in DAX refers to the environment in which expressions are evaluated, determined by the filters applied to the data model and by the relative position of rows and columns during the calculation of a formula.

Knowing this is important because it equips us with the ability to generate dynamic analyses and to solve potential formula problems.

Here you can see the differences between row context and filter context:

Differentiation Category Row Context Filter Context
Nature of the Context Row context focuses on evaluating each row individually during iterative calculations, such as in iterative functions (SUMX, AVERAGEX, etc.). Filter context, on the other hand, refers to the filters applied to the dataset before calculating a DAX expression, determining which data is included in the calculations.
Application Row context is used to perform custom calculations for each row of data, allowing detailed analysis at the row level. Filter context, in contrast, defines which data is available for calculation in the first place, reducing the dataset before calculations are applied.
Generation Row context is generated through iterative functions and expressions that create a specific row context for each iteration, such as SUMX or AVERAGEX. Filter context is generated through filters applied directly to visualizations or through filtering functions like FILTER, which set a filter on the dataset before calculating a DAX expression.

Context transition refers to the ability of certain functions, like CALCULATE(), to switch between row and filter contexts.

2. Building a Good Data Model

Helps DAX function correctly. This includes establishing proper relationships between tables, correctly defining filter directions, and logically organizing data. A solid data model is the foundation for success in DAX.Now, if you want to start off on the right foot with DAX, here are a few tips when creating your expressions:

  1. Use clear and explanatory names for the measures you create.
  2. Indent your code and comment on it, this ensures order and clarity and documents your code.
  3. Always create a measures table in your reports.
  4. You can create folders to classify and organize your measures.
  5. For performance, avoid or minimize the use of calculated columns and tables in your reports, especially in very large datasets.

Understanding context evaluation and data modeling are elements that can help you make the most of this powerful expression language.Tell me, have you heard about DAX before? Would you like to delve deeper into this language?

PS: To indent your code, I recommend the DAX FORMATTER tool, where you paste your code, and it automatically organizes it for you to then include it in your report.Contact us so we can help you analyze your needs and offer you a tailored solution.

At Raona, we have been working for over 20 years for large organizations. With more than 100 completed projects and 200 companies assisted, we are the most awarded company in intranet projects in Spain. Contact us, and we will assist you without any commitment.

    Carol Otero Gómez

    I am a proactive, self-taught data analyst with expertise in Power BI and financial analytics. My focus is on turning complex data into valuable information to drive strategic decision making. I enjoy collaborating in multi-disciplinary teams and cultivating strong interpersonal relationships.

    Compartir en Redes Sociales