Category: excel Dax

Excel DAX – Standard Parameters ”; Previous Next DAX has standard parameter names to facilitate the usage and understanding of the DAX functions. Further, you can use certain prefixes to the parameter names. If the prefix is clear enough, you can use the prefix itself as the parameter name. Standard Parameter Names Following are the DAX standard parameter names − Sr.No. Parameter Name & Description 1 expression Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context). 2 value Any DAX expression that returns a single scalar value where the expression is to be evaluated exactly once before all other operations. 3 table Any DAX expression that returns a table of data. 4 tableName The name of an existing table using standard DAX syntax. It cannot be an expression. 5 columnName The name of an existing column using standard DAX syntax, usually fully qualified. It cannot be an expression. 6 name A string constant that will be used to provide the name of a new object. 7 order An enumeration used to determine the sort order. 8 ties An enumeration used to determine the handling of tie values. 9 type An enumeration used to determine the data type for PathItem and PathItemReverse. Prefixing Parameter Names You can qualify a parameter name with a prefix − The prefix should be descriptive of how the argument is used. The prefix should be in such a way that ambiguous reading of the parameter is avoided. For example, Result_ColumnName − Refers to an existing column used to get the result values in the DAX LOOKUPVALUE () function. Search_ColumnName − Refers to an existing column used to search for a value in the DAX LOOKUPVALUE () function. Using Only the Prefix as a Parameter You can omit the parameter name and use only the prefix, if the prefix is clear enough to describe the parameter. Omitting the parameter name and using only the prefix can sometimes help in avoiding the clutter in reading. For example, Consider DATE (Year_value, Month_value, Day_value). You can omit the parameter name – value, that is repeated thrice and write it as DATE (Year, Month, Day). As you can observe, by using only the prefixes, the function is more readable. However, sometimes the parameter name and the prefix have to be present for clarity. For example, Consider Year_columnName. The parameter name is ColumnName and the prefix is Year. Both are required to make the user understand that the parameter requires a reference to the existing column of years. Print Page Previous Next Advertisements ”;

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 – 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 ”;