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:
They can affect the result of an expression. These include:
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.
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:
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
Compartir en Redes Sociales
© Raona 2023. All Rights Reserved