Category: Big Data & Analytics

Excel DAX – Evaluation Context ”; Previous Next In DAX, context is an important term that you should be aware of, while writing DAX formulas. Also referred to as evaluation context, DAX context is used to determine the evaluation of a DAX formula and the corresponding result. This means, the results of a DAX formula can vary according to the context. You should clearly understand how a specific DAX context is used and how the results can be different. Evaluation context enables you to perform dynamic analysis, in which the results of a DAX formula can change to reflect the current row or a cell selection and also any related data. Understanding context and using context effectively are very important to build powerful DAX formulas, perform dynamic data analysis, and troubleshoot problems in DAX formulas. Evaluation contexts are the basis of all of the advanced features of DAX that you need to master to create complex data analysis reports. As you keep referencing to DAX functions for relevant usage in DAX formulas, you need to refer to this chapter on DAX context to obtain clarity on the results. Types of Context in DAX DAX supports the following evaluation contexts − Row Context Filter Context When a DAX formula is evaluated, all the contexts will be taken into account and are applied as relevant. The contexts exist together and the result of the formula will be different based on the context that is used while calculating a value. For example, when you select fields for rows, columns, and filters in a PivotTable, the subtotals are dynamically calculated based on which row and which column the subtotal/total is associated with and the values in the rows and columns are determined by the filters used. Row Context Row context means that the DAX formula or the DAX function knows which row of the table it is referencing at any point in time. You can consider row context as the current row. The formula will get calculated row-by-row with the row context. Some DAX functions (e.g., the X-functions, FILTER ()) and all calculated columns have a row context. For example, if you create a calculated column Year with the DAX formula = YEAR ([Date]), the values of the calculated column are obtained by applying the given DAX formula on the given column in the table, row by row. This means that if you have created a calculated column, the row context consists of the values in each individual row and the values in the columns that are related to the current row, as determined by the DAX formula used. Though the DAX formula does not contain the reference to a row, DAX implicitly understands the row context while calculating values. DAX creates a row context automatically when you define a calculated column and all the calculated values with the DAX formula used will appear in the calculated column. In contrast, when you have a DAX function such as SUMX, the values calculated row by row get summed up and only the final result will be displayed. That is, the intermediate values are discarded. When you have related tables, the row context determines which rows in the related table are associated with the current row. However, the row context does not propagate through relationships automatically. You have to use the DAX functions – RELATED and RELATEDTABLE for this. Multiple Row Context DAX has iterator functions like SUMX. You can use these functions to nest row contexts. With this, programmatically you can have a recursion over an inner loop and an outer loop, where you can have multiple current rows and current row contexts. For example, you can use the DAX function Earlier () that stores the row context from the operation that preceded the current operation. This function stores two sets of context in memory – one set of context represents the current row for the inner loop of the formula, and another set of context represents the current row for the outer loop of the formula. DAX automatically feeds the values between the two loops so that you can create complex aggregates. For an example, refer to the scenario – Creating a DAX Formula that Dynamically Ranks Values in the chapter Scenarios – Ranking and Comparing Values. Filter Context Filter context refers to any filtering that is applied to the Data Model in DAX. Filter context is created by a PivotTable and also by the DAX functions. Filter Context Created by a PivotTable Filter Context created by a PivotTable is the natural filtering that is applied by the selections made on the PivotTable fields from the following − Rows Columns Filters Slicers The filter context created by a PivotTable, filters the underlying tables in the Data Model. If the tables are related, then the filters flow down from the lookup tables to data tables. That means, you can filter the data tables based on the results from the lookup tables. The filter propagation does not happen the other way round. However, you can use DAX formulas to filter the lookup tables based on the results from the data tables. Filter Context Created by DAX Functions You can use DAX Filter functions to define calculated fields and calculated columns, containing filter expressions that control the values used by the DAX formula. These calculated fields and calculated columns then become part of the PivotTable fields list and you can add them to the PivotTable. You can also selectively clear the filters on particular columns with these DAX Filter functions. An example of a powerful DAX Filter function to create Filter Context is CALCULATE (). For an example, refer to the chapter Scenarios – Performing Complex Calculations. Filter Context as an Addition to Row Context Row context does not automatically create a filter context. You can achieve the same with the DAX formulas containing DAX Filter functions. Print Page Previous Next

Excel DAX Tutorial PDF Version Quick Guide Resources Job Search Discussion DAX (Data Analysis Expressions) is a formula language that helps you create new information from the data that already exists in your Data Model. DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making. It is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX is the formula language associated with the Data Model of Excel Power Pivot. Audience This tutorial has been designed for all those readers who depend heavily on MS-Excel to prepare charts, tables, and professional reports that involve complex data. It will help all those readers who use MS-Excel regularly to analyze data. Professionals who use data modeling and data analysis for reporting and decision-making purposes will benefit from this. Prerequisites This tutorial is an extension to Excel Power Pivot tutorial, hence it is a good idea to brush up on the Excel Power Pivot tutorial before you delve into DAX. Knowledge of Excel Functions and Excel Formulas is not necessary for this tutorial, as DAX is entirely for the Data Model in the Power Pivot window. Print Page Previous Next Advertisements ”;

Excel DAX – Deleting a Calculated Field ”; Previous Next You can delete both explicit and implicit calculated fields. There are several ways of doing so, which you will learn in this chapter. However, you need to remember the following points before deleting a calculated field − An explicit calculated field can be used in more than one PivotTable and/or PivotChart. Hence, you need to make sure that deleting the explicit calculated field does not affect any of the reports that you have already generated. An explicit calculated field can be used in the calculations of other explicit calculated fields. Hence, you need to make sure that the explicit calculated field is not used in any of the calculations of other explicit calculated fields. An implicit calculated field is limited to the PivotTable or PivotChart where it is used. Hence, before deleting an implicit calculated field, it is just sufficient to make sure that it can be deleted from the corresponding PivotTable or PivotChart. Creating an implicit calculated field is simpler than creating an explicit calculated field. Hence, more caution is required before deleting an explicit calculated field. You cannot create an explicit calculated field, if the name conflicts with the name of an implicit calculated field. Hence, you might have to delete that implicit calculated field before creating the explicit calculated field. Deleting an Explicit Calculated Field in the Data Model You can delete an explicit calculated field either in the data view or the diagram view in the Data Model. Deleting an Explicit Calculated Field in the Data View Locate the calculated field in the calculations area in the Data View. Right-click the calculated field. Click Delete in the dropdown list. Message appears for delete confirmation. Click Delete from Model. The explicit calculated field will get deleted. Deleting an Explicit Calculated Field in the Diagram View Locate the calculated field in the data table in the Diagram View. Right-click the calculated field name. Click Delete in the dropdown list. Message appears for delete confirmation. Click Delete from Model. The explicit calculated field will get deleted and it will not be seen in the fields list of the data table. Deleting an Explicit Calculated Field in the Excel Window You can delete an explicit calculated field from the Excel window as follows − Click the POWERPIVOT tab on the Ribbon. Click Calculated Field in the Calculations group. Click Manage Calculated Fields in the dropdown list. Manage Calculated Fields dialog box appears. Click the explicit calculated field name. Click the Delete button. Confirmation message for deletion appears. Click Yes. Information message that the Data Model is changed appears at the top. Click the Close button in the dialog box. The explicit calculated field will get deleted and it will not be seen in the PivotTable/PivotChart Fields list in the workbook. Deleting an Implicit Calculated Field You can delete an implicit calculated field either in the data view or the diagram view in the Data Model. Deleting an Implicit Calculated Field in the Data View Locate the calculated field in the calculations area in the Data View. Right-click the calculated field. Click Delete in the dropdown list. Message appears for delete confirmation. Click Delete from Model. The implicit calculated field will get deleted. Deleting an Implicit Calculated Field in the Diagram View Locate the calculated field in the data table in the Diagram View. Right-click the calculated field name. Click Delete in the dropdown list. Message appears for delete confirmation. Click Delete from Model. The implicit calculated field will get deleted and it will not be seen in the fields list of the data table. Print Page Previous Next Advertisements ”;

Excel DAX – Recalculating DAX Formulas ”; Previous Next Recalculation of a DAX formula is required to reflect changes in the data and changes in the formula itself. However, recalculating a DAX formula involves performance cost. Even then, to obtain accurate results, recalculation is essential. During recalculation, column dependencies are checked and you will be notified if a column has changed, if the data is invalid or if an error has appeared in a DAX formula that used to work. Types of Recalculation You have two options for recalculating DAX formulas − Automatic Recalculation Mode (default) Manual Recalculation Mode By default, Power Pivot automatically recalculates as required while optimizing the time required for processing. However, you can choose to update calculations manually, if you are working with complex formulas or very large data sets and want to control the timing of updates. Both automatic and manual modes of recalculating DAX formulas have advantages. However, the recommended way is to use automatic recalculation mode. This way you can keep the Power Pivot data in sync and prevent problems caused by deletion of data, changes in names or data types or missing dependencies. Recalculating DAX Formulas Automatically If you choose the default mode of recalculating DAX formulas, i.e. recalculating automatically, any changes to data that would cause the result of any DAX formula to change will trigger recalculation of the entire column that contains the DAX formula. The following changes always require recalculation of DAX formulas − Values from an external data source have been refreshed. The DAX formula itself is changed. Names of tables or columns that are referenced in the DAX formula have been changed. Relationships between tables have been added, modified or deleted. New calculated fields or calculated columns have been added. Changes have been made to other DAX formulas within the workbook, so columns or calculations that depend on those DAX formulas need to be recalculated. Rows have been inserted or deleted in the table. You applied a filter that requires execution of a query to update the data set. The filter could have been applied either in a DAX formula or as part of a PivotTable or PivotChart. When to Use Manual Recalculation Mode? You can use manual recalculation mode until you are ready with all your required DAX formulas in your workbook. This way, you can avoid incurring the cost of computing formula results on the workbook that is still in the draft state. You can use manual recalculation of DAX formulas in the following conditions − You are designing a DAX formula by using a template and want to change the names of the columns and tables used in the DAX formula before you validate it. You know that some data in the workbook has changed but you are working with a different column that has not changed so you want to postpone a recalculation. You are working in a workbook that has many dependencies and want to defer recalculation till you are sure that all the necessary changes have been made. But, you should be aware that as long as the workbook is configured to manual recalculation mode, any validation or checking of formulas is not performed. This will result in the following − Any new formulas that you add to the workbook will be flagged as containing an error. No results will appear in the new calculated columns. Configuring the Workbook for Manual Recalculation As you have learnt, automatic recalculation is the default mode in the Data Model of any workbook. To configure a workbook for manual recalculation, do the following − Click the Design tab on the Ribbon in the Power Pivot window. Click the Calculation Options in the Calculations group. Click the Manual Calculation Mode in the dropdown list. Recalculating DAX Formulas Manually To recalculate the DAX formulas manually, do the following − Click the Design tab on the Ribbon in the Power Pivot window. Click the Calculation Options field in the Calculations group. Click the Calculate Now field in the dropdown list. Print Page Previous Next Advertisements ”;

Excel DAX – Calculated Columns ”; Previous Next A calculated column is a column that you add to an existing table in the Data Model of your workbook by means of a DAX formula that defines the column values. Instead of importing the values in the column, you create the calculated column. You can use the calculated column in a PivotTable, PivotChart, Power PivotTable, Power PivotChart or Power View report just like any other table column. Understanding Calculated Columns The DAX formula used to create a calculated column is like an Excel formula. However, in DAX formula, you cannot create different formulas for different rows in a table. The DAX formula is automatically applied to the entire column. For example, you can create one calculated column to extract Year from the existing column – Date, with the DAX formula − = YEAR ([Date]) YEAR is a DAX function and Date is an existing column in the table. As seen, the table name is enclosed in brackets. You will learn more about this in the chapter – DAX Syntax. When you add a column to a table with this DAX formula, the column values are computed as soon as you create the formula. A new column with the header CalculatedColumn1 filled with Year values will get created. Column values are recalculated as necessary, such as when the underlying data is refreshed. You can create calculated columns based on existing columns, calculated fields (measures), and other calculated columns. Creating a Calculated Column Consider the Data Model with the Olympics Results as shown in the following screenshot. Click the Data View. Click the Results tab. You will be viewing the Results table. As seen in the above screenshot, the rightmost column has the header – Add Column. Click the Design tab on the Ribbon. Click Add in the Columns group. The pointer will appear in the formula bar. That means you are adding a column with a DAX formula. Type =YEAR ([Date]) in the formula bar. As can be seen in the above screenshot, the rightmost column with the header – Add Column is highlighted. Press Enter. It will take a while (few seconds) for the calculations to be done. Please wait. The new calculated column will get inserted to the left of the rightmost Add Column. As shown in the above screenshot, the newly inserted calculated column is highlighted. Values in the entire column appear as per the DAX formula used. The column header is CalculatedColumn1. Renaming the Calculated Column To rename the calculated column to a meaningful name, do the following − Double-click on the column header. The column name will be highlighted. Select the column name. Type Year (the new name). As seen in the above screenshot, the name of the calculated column got changed. You can also rename a calculated column by right-clicking on the column and then clicking on Rename in the dropdown list. Just make sure that the new name does not conflict with an existing name in the table. Checking the Data Type of the Calculated Column You can check the data type of the calculated column as follows − Click the Home tab on the Ribbon. Click the Data Type. As you can see in the above screenshot, the dropdown list has the possible data types for the columns. In this example, the default (Auto) data type, i.e. the Whole Number is selected. Errors in Calculated Columns Errors can occur in the calculated columns for the following reasons − Changing or deleting relationships between the tables. This is because the formulas that use columns in those tables will become invalid. The formula contains a circular or self-referencing dependency. Performance Issues As seen earlier in the example of Olympics results, the Results table has about 35000 rows of data. Hence, when you created a column with a DAX formula, it had calculated all the 35000+ values in the column at once, for which it took a little while. The Data Model and the tables are meant to handle millions of rows of data. Hence, it can affect the performance when the DAX formula has too many references. You can avoid the performance issues doing the following − If your DAX formula contains many complex dependencies, then create it in steps saving the results in new calculated columns, instead of creating a single big formula at once. This enables you to validate the results and assess the performance. Calculated columns need to be recalculated when data modifications occur. You can set the recalculation mode to manual, thus saving frequent recalculations. However, if any values in the calculated column are incorrect, the column will be grayed out, until you refresh and recalculate the data. Print Page Previous Next Advertisements ”;