Understanding how DAX works

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:

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.