How Row context and filter context impact data evaluation index.

 DAX as a useful language for generating business insights using formulas. However, data analysts need to understand that DAX generates insights from data based on the context of that data. In this video, you'll explore the concepts of row and filter context and discover how they impact data evaluation index. Adventure Works needs to answer business specific questions like what are the total sales for each product, and what are the top selling items by category. It can generate these insights using DAX. DAX formulas answer these questions by evaluating the relevant data according to its row and filter context. Let's find out more about the relationships between DAX and context. DAX computes formulas within a context. The evaluation context of a DAX formula is the surrounding area of the cell in which DAX evaluates and computes the formula. The surrounding area is determined by the set of rules and filters to be evaluated in a DAX expression. It determines which subset of data is used to perform calculations. DAX expressions adapt or refer to the context for evaluating dynamic and contexts aware results. Let's begin with an overview of row context. Row context refers to the tables current row being evaluated within a calculation. When a DAX expression is evaluated for a specific row, it considers the values of the columns in that row as the context of the calculation. This allows for calculations to be performed at row level and it's especially useful for iterating two rows within a table. For instance, if you create a formula for a calculated column, the row context for your formula includes the values from all the columns in the current row. Let's demonstrate the concept using Adventure Works Sales table. The table contains sales data for multiple products over one month stored within the following columns, days, product, category, quantity, and price. Adventure Works wants to create a total sales calculated column that shows the total sales data for each product in the table. The company can use a DAX formula to multiply the quantity data in the quantity column by the price data and the price column for each item. The formula iterates through the relevant quantity and price column values at the row level and returns the results and the total sales calculated column. In other words, the formula calculates the new values via row context. Next, let's review filter context. As the name suggests, filter context refers to the filter constraints applied to the data before it's evaluated by the DAX expression. In the previous example, a different result was produced in each cell because the same DAX expression was evaluated against different subsets of data. However, with filter context, you can determine which rows or subsets should be included or excluded from the calculation. Let's demonstrate filter context using the Adventure Works Sales table. Adventure Works must calculate the total sales for all items in Category X. The company can create a DAX formula containing filters that targets all sales recorded against Category X. Once the formula is executed, it iterates through each row and retrieves only the data with the value of X. Row and filter context also interact with each other to produce results. When a DAX expression is evaluated, it first considers the filter context, then the row context takes effect. Let's demonstrate how this occurs with Adventure Works. The company can use the filter context to narrow it sales data to the selected region. The row context then iterates each row in the filtered results and calculates the sales totals. As you've just discovered, a filter applied on a table column affects all table rows, filtering rows that satisfy that filter. If you apply two or more filters to columns in the same table, they are executed under a logical end condition. This means only the rows satisfying all the filters are processed by the DAX expression in that filter context. Be careful when applying a filter in a large data model with multiple tables, a filter context automatically propagates through the relationships between the tables in the data model based on the selected cross filter direction of the relationships. In this example, this means that when data is filtered in the sales order table, then data in the related tables is also filtered. You can disconnect the tables to prevent propagation. A rule context, on the other hand, doesn't automatically propagate through a data models relationships. If you have a row context in a table, you can iterate the roles of a table on the many side of a one-to-many or many-to-many relationship using the related table function. You can also access the rows of the parent table using the related function of DAX. Understanding the context of DAX expressions at the row unfiltered level is important as you continue to build data models for reporting and visualization. Contexts defects how DAX interprets and analyze your data. So always consider the context when creating and executing your DAX formulas.