Category: excel Dax

Excel DAX – Ranking and Comparing Values ”; Previous Next If you want to show only the top n number of items in a column or PivotTable, you have the following two options − You can select n number of top values in the PivotTable. You can create a DAX formula that dynamically ranks values and then uses the ranking values in a Slicer. Applying a Filter to Show only the Top Few Items To select n number of top values for display in the PivotTable, do the following − Click the down arrow in the row labels heading in the PivotTable. Click the Value Filters in the dropdown list and then click Top 10. Top 10 Filter (<column name>) dialog box appears. Under Show, select the following in the boxes from left to right. Top 18 (The number of top values that you want to display. The default is 10.) Items. In the by box, select Medal Count. Click OK. The top 18 values will be displayed in the PivotTable. Advantages and Disadvantages of Applying Filter Advantages It is simple and easy to use. Suitable for tables with large number of rows. Disadvantages The filter is solely for display purposes. If the data underlying the PivotTable changes, you must manually refresh the PivotTable to see the changes. Creating a DAX Formula That Dynamically Ranks Values You can create a calculated column using a DAX formula that contains the ranked values. You can then use a slicer on the resulting calculated column to select the values to be displayed. You can obtain a rank value for a given value in a row by counting the number of rows in the same table having a value larger than the one that is being compared. This method returns the following − A zero value for the highest value in the table. Equal values will have the same rank value. If n number of values are equal, the next value after the equal values will have a nonconsecutive rank value adding up the number n. For example, if you have a table ‘Sales’ with sales data, you can create a calculated column with the ranks of the Sales Amount values as follows − = COUNTROWS (FILTER (Sales, EARLIER (Sales [Sales Amount]) < Sales [Sales Amount]) ) + 1 Next, you can insert a Slicer on the new calculated column and selectively display the values by ranks. Advantages and Disadvantages of Dynamic Ranks Advantages The ranking is done in the table and not on a PivotTable. Hence, can be used in any number of PivotTables. DAX formulas are calculated dynamically. Hence, you can always be sure that the ranking is correct even if the underlying data has changed. Since the DAX formula is used in a calculated column, you can use the ranking in a Slicer. Suitable for tables with large number of rows. Disadvantages Since the DAX calculations are computationally expensive, this method might not be suitable for tables with large number of rows. Print Page Previous Next Advertisements ”;

Troubleshooting DAX Formula Recalculation ”; Previous Next Whenever changes occur in the Data Model of your workbook, Power Pivot performs an analysis of the existing data to determine whether recalculation is required and performs the update in the most efficient way possible. Power Pivot handles the following, during recalculation of DAX formulas − Dependencies Sequence of recalculation for dependent columns Transactions Recalculation of volatile functions Dependencies When a column depends on another column, and the contents of that other column change in any way, all related columns might need to be recalculated. Power Pivot always performs a complete recalculation for a table, because a complete recalculation is more efficient than checking for changed values. The changes that trigger recalculation might include deleting a column, changing the numeric data type of a column or adding a new column. These changes are considered as major changes. However, seemingly trivial changes, such as changing the name of a column might also trigger recalculation. This is because the names of the columns are used as identifiers in the DAX formulas. In some cases, Power Pivot may determine that columns can be excluded from recalculation. Sequence of Recalculation for Dependent Columns Dependencies are calculated prior to any recalculation. If there are multiple columns that depend on each other, Power Pivot follows the sequence of dependencies. This ensures that the columns are processed in the right order at the maximum speed. Transactions Operations that recalculate or refresh data take place as a transaction. This means that if any part of the refresh operation fails, the remaining operations are rolled back. This is to ensure that data is not left in a partially processed state. However, you cannot manage the transactions as you do in a relational database or create checkpoints. Recalculation of Volatile Functions DAX functions such as NOW, RAND, or TODAY do not have fixed values and are referred to as volatile functions. If such DAX functions are used in a calculated column, the execution of a query or filtering will usually not cause them to be re-evaluated to avoid performance problems. The results for these DAX functions are only recalculated when the entire column is recalculated. These situations include refresh from an external data source or manual editing of data that causes re-evaluation of DAX formulas that contain these functions. However, such functions will always be recalculated if the functions are used in the definition of a Calculated Field. Print Page Previous Next Advertisements ”;

Excel DAX – Updating Data in the Data Model ”; Previous Next DAX is used for calculations on the data in the Data Model in Excel Power Pivot. DAX enables data modeling and reporting activities to be handled in an effective way. However, this requires updating the data in the Data Model from time to time so as to reflect the current data. You can import data from an external data source into the Data Model of your workbook by establishing a data connection. You can update the data from the source whenever you choose. This option is handy if you are getting data from relational databases that contain live sales information or data feeds that are updated several times a day. Different Ways of Updating Data in the Data Model You can update the data in the Data Model in the following ways − Refreshing data in the Data Model from time to time. Making changes to data sources, such as connection properties. Updating the data in the Data Model after the source data has changed. Filtering the data to selectively load rows from a table in the data source. Refreshing Data in the Data Model In addition to getting updated data from an existing source, you will need to refresh data in your workbook whenever you make changes to the schema of the source data. These changes can include adding columns or tables, or changing the rows that are imported. Note that addition of data, changing data, or editing filters always triggers recalculation of DAX formulas that depend on that data source. Refer to the chapter – Recalculating DAX Formulas for details. You have two types of data refresh in Data Model − Manual Refresh If you choose manual refresh option, you can refresh the data in the Data Model manually at any time. You can refresh all data, which is the default, or you can manually choose the tables and columns to refresh for individual data sources. Automatic or Scheduled Refresh If you have published your workbook to a PowerPivot Gallery or SharePoint site that supports PowerPivot, you or the SharePoint administrator can create a schedule for automatically updating the data in the workbook. In such a case, you can schedule unattended data refresh on the server. Manually Refreshing an Existing Data Source You can manually refresh your data any time, if you need to update the data from an existing data source or get the recent data for designing new DAX formulas. You can refresh a single table, all tables that share the same data connection or all tables in the Data Model. If you have imported data from a relational data source, such as SQL Server and Oracle, you can update all the related tables in one operation. The operation of loading new or updated data into the Data Model often triggers recalculation of DAX formulas, both of which might require some time to complete. Hence, you should be aware of the potential impact before you change data sources or refresh the data that is obtained from the data source. To refresh data for a single table or all tables in a Data Model, do the following − Click the Home tab on the Ribbon in the Power Pivot window. Click Refresh. Click Refresh in the dropdown list for refreshing the selected table. Click Refresh All in the dropdown list for refreshing all the tables. To refresh data for all tables that use the same connection in a Data Model, do the following − Click the Home tab on the Ribbon in Power Pivot window. Click the Existing Connections in the Get External Data group. Existing Connections dialog box appears. Select a connection. Click the Refresh button. Data Refresh dialog box appears and data refresh progress information is displayed as the PowerPivot engine reloads data from the selected table or from all tables from the data source. There are three possible outcomes − Success − Reports on the number of rows imported into each table. Error − An error can occur if the database is offline, you no longer have permissions. A table or column is deleted or renamed in the source. Cancelled − This means Excel did not issue the refresh request, probably because refresh is disabled on the connection. Click the Close button. Changing a Data Source To change the data in your Data Model, you can edit the connection information or update the definition of the tables and columns used in your Data Model in the Power Pivot window. You can make the following changes to the existing data sources − Connections Edit the database name or the server name. Change the name of the source text file, spreadsheet, or data feed. Change the location of the data source. For relational data sources, change the default catalog or initial catalog. Change the authentication method or the credentials used to access the data. Edit advanced properties on the data source. Tables Add or remove a filter on the data. Change the filter criteria. Add or remove tables. Change the table names. Edit mappings between tables in the data source and tables in the Data Model. Select different columns from the data source. Columns Change the column names. Add new columns. Delete columns from the Data Model (does not affect the data source). You can edit the properties of an existing data source in the following ways − You can change the connection information, including the file, feed, or database used as a source, its properties or other provider specific connection options. You can change the table and column mappings and remove references to columns that are no longer used. You can change the tables, views, or columns that you get from the external data source. Modifying a Connection to an Existing Data Source You can modify the connection that you have created to an external data source by changing the external data source used by the current connection. However,

Excel DAX – Formulas ”; Previous Next DAX is a formula language for creating custom calculations in Power PivotTables. You can use the DAX functions that are designed to work with relational data and perform dynamic aggregation in DAX formulas. DAX formulas are very similar to Excel formulas. To create a DAX formula, you type an equal sign, followed by a function name or expression and any required values or arguments. DAX Functions vs. DAX Formulas DAX formulas can include DAX functions and leverage their usage. This is where DAX formulas tend to differ from DAX functions in important ways. A DAX function always reference a complete column or a table. If you want to use only particular values from a table or column, you can add filters to the formula. If you want to customize calculations on a row by row basis, Power Pivot provides functions that let you use the current row value or a related value to perform calculations that vary by context. DAX includes a type of function that returns a table as its result, rather than a single value. These functions can be used to provide input to other functions, thus calculating values for entire tables or columns. Some DAX functions provide time intelligence, which lets you create calculations using meaningful ranges of dates and compare the results across parallel periods. Understanding DAX Formula Syntax Every DAX formula has the following syntax − Each formula must begin with an equal sign. To the right of the equal sign, you can either type or select a function name, or type an expression. The expression can contain table names and column names connected by DAX operators. Following are some valid DAX formulas − [column_Cost] + [column_Tax] = Today () Understanding IntelliSense Feature DAX provides the IntelliSense feature that will enable you to write DAX formulas promptly and correctly. With this feature, you need not type the table, column, and function names completely, but select the relevant names from the dropdown list while writing a DAX formula. Begin to type the first few letters of the function name. AutoComplete displays a list of available functions with the names beginning with those letters. Place the pointer on any of the function names. IntelliSense tooltip will be displayed giving you the use of the function. Click the function name. The function name appears in the formula bar and the syntax is displayed, which will guide you as you select the arguments. Type the first letter of the table name that you want. AutoComplete displays a list of available tables and columns with the names beginning with that letter. Press TAB or click the name to add an item from the AutoComplete list to the formula. Click the Fx button to display a list of available functions. To select a function from the dropdown list, use the arrow keys to highlight the item and click OK to add the function to the formula. Supply the arguments to the function by selecting them from a dropdown list of possible tables and columns or by typing in required values. Usage of this handy IntelliSense feature is highly recommended. Where to Use DAX Formulas? You can use DAX formulas in creating calculated columns and calculated fields. You can use DAX formulas in calculated columns, by adding a column and then typing an expression in the formula bar. You create these formulas in the PowerPivot window. You can use DAX formulas in calculated fields. You create these formulas − In the Excel window in the Calculated Field dialog box, or In the Power Pivot window in the calculation area of a table. The same formula can behave differently depending on whether the formula is used in a calculated column or a calculated field. In a calculated column, the formula is always applied to every row in the column, throughout the table. Depending on the row context, the value might change. In a calculated field, however, the calculation of results is strongly dependent on the context. That is, the design of the PivotTable and the choice of row and column headings affects the values that are used in calculations. It is important to understand the concept of context in DAX to write DAX formulas. This can be a bit difficult in the beginning of your DAX journey, but once you get a grasp on it, you can write effective DAX formulas that are required for complex and dynamic data analysis. For details, refer to the chapter – DAX Context. Creating a DAX Formula You have already learnt about the IntelliSense feature in a previous section. Remember to use it while creating any DAX formula. To create a DAX formula, use the following steps − Type an equal sign. To the right of the equal sign, type the following − Type the first letter of a function or table name and select the complete name from the dropdown list. If you have chosen a function name, type parenthesis ‘(‘. If you have chosen the table name, type bracket ‘[‘. Type the first letter of the column name and select the complete name from the dropdown list. Close the column names with ‘]’ and function names with ‘)’. Type a DAX operator between expressions or type ‘,’ to separate function arguments. Repeat steps 1 – 5 till the DAX formula is complete. For example, you want to find the total sales amount in the East region. You can write a DAX formula as shown below. East_Sales is the name of the table. Amount is a column in the table. SUM ([East_Sales[Amount]) As already discussed in the chapter – DAX Syntax, it is a recommended practice to use the table name along with the column name in every reference to any column name. This is termed as – “the fully qualified name”. The DAX formula can vary based on whether it is for a calculated field or calculated column. Refer to the sections below for

Excel DAX – Overview ”; Previous Next DAX stands for Data Analysis Expressions. DAX is a formula language and 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. It is not a programming language, but is a formula language that allows the users to define custom calculations in calculated columns and calculated fields (also known as measures). DAX helps you create new information from the data that is already present in your Data Model. DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making. DAX includes some of the functions that are used in Excel formulas, but with modified functionality and additional functions that are designed to work with relational data and perform dynamic aggregation. Importance of DAX The foundation of DAX is the Data Model that is the Power Pivot database in Excel. Data Model consists of tables between which relationships can be defined so as to combine the data from different sources. The data connections to the Data Model can be refreshed as and when the source data changes. Data Model makes the use of the Power Pivot xVelocity in-memory analytics engine (VertiPaq) that makes the data operations to be as quick as possible in addition to accommodating several thousands of rows of data. For more information on Data Model, refer to the tutorial – Power Pivot. DAX in conjunction with Data Model enables several power features in Excel – Power Pivot, Power PivotTables, Power PivotCharts and Power View. You can use DAX to solve a number of basic calculations and data analysis problems. DAX is also useful in Power BI to create a new Power BI Desktop file and import some data into it. Further, DAX formulas provide capabilities such as analyzing growth percentage across product categories and for different date ranges, calculating year-over-year growth compared to market trends and many others as well. Learning how to create effective DAX formulas will help you get the most out of your data. When you get the information you need, you can begin to solve real business problems that affect your bottom line. This is the power in Power BI and DAX will help you get there. Prerequisites for this Tutorial This tutorial is an extension to Excel Power Pivot tutorial, where you have learnt about the Power Pivot feature, Data Model, Relationships, Power PivotTables, Power Pivot Charts, etc. It would be a good idea to brush up on this tutorial before you delve into DAX as this tutorial is more on the DAX language wherein you write formulas for the analysis of data in the Data Model and report those results. This tutorial also introduces DAX Functions that are like Excel Functions, but with some variations. A comparison of Excel Functions and DAX Functions is provided to help you distinguish both. Similarly, Excel formulas and DAX formulas are compared and the similarities and differences are discussed. A good understanding of these differences would help you in writing effective DAX formulas efficiently. 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. You will get into an Excel worksheet only to view the Power PivotTables, Power Pivot Charts and Power View visualizations that are based on Data Model. However, if you are an Excel professional with good amount of knowledge in Excel Functions and Formulas, better make a note of what is mentioned in the previous section and the details given in the course of this tutorial. Calculated Columns Calculated columns are the columns that you can add to a table in the Data Model, by means of a DAX formula. You have already learnt about them in Excel Power Pivot tutorial, but you will learn in detail in the chapter – Calculated Columns as DAX is all about calculated columns, calculated fields, and DAX functions. Calculated Fields / Measures You cannot change the values in the tables in the Data Model by editing. However, you can add calculated fields to a table that can be used in the Power PivotTables. The calculated fields are defined by giving a name and by defining a DAX formula. For details, refer to the chapter – Calculated Fields. The calculated fields were named as measures in the Excel versions prior to Excel 2013. They are renamed back to measures in Excel 2016. In this tutorial, we will refer them as calculated fields. But, note that the terms – calculated fields and measures – are synonymous and refer to the same in all aspects. You can edit a calculated field after it is defined and stored. You can change the DAX formula used in the definition or you can rename the calculated field. You will learn about this in the chapter – Editing a Calculated Field. You can delete a calculated field. Refer to the chapter – Deleting a Calculated Field. DAX Formulas DAX formulas form the heart of the DAX language. You can create calculated fields and calculated columns by defining them with DAX formulas. You can write DAX formulas for the data analysis operations. DAX formulas do not refer to the individual cells or range of cells in the table, but refer to the tables and columns in the Data Model. A column in a table in the Data Model must contain the same data type. DAX formulas contain the tables, columns, calculated columns, calculated fields, DAX operators, and DAX functions. Refer to the chapter – DAX Formulas to learn in detail. DAX Syntax As is the case with any language, DAX, the formula language also has a syntax. Your DAX formulas should follow DAX syntax, or else, you will either get errors at design time or at run time or you

Excel DAX – Useful Resources ”; Previous Next The following resources contain additional information on Excel DAX. Please use them to get more in-depth knowledge on this. Useful Video Courses Excel – A Step by Step Complete Course 56 Lectures 6 hours TELCOMA Global More Detail Excel Makro ve VBA Eğitimi-1 37 Lectures 6.5 hours Volkan Yurtseven More Detail Excel Pivot Tables – Crash Course Most Popular 18 Lectures 47 mins EdSolver Learning More Detail Learn MS Excel, Data Analysis, and Financial Analysis 50 Lectures 3 hours Tayana Grubisic More Detail Excel Analytics – Data Analysis with Pivot-Tables and Charts 47 Lectures 2.5 hours Blink Data More Detail Learn microsoft excel course for data analysis Zero to hero 50 Lectures 2 hours ADITYA More Detail Print Page Previous Next Advertisements ”;

Excel DAX – Using Time Intelligence ”; Previous Next You have learnt about the DAX powerful feature Time Intelligence in the chapter – Understanding Time Intelligence. In this chapter, you will learn how to use DAX time intelligence functions in various scenarios. DAX time intelligence functions include − Functions that help you retrieve dates or date ranges from your data, which are used to calculate values across similar periods. Functions that work with standard date intervals, to allow you to compare values across months, years, or quarters. Functions that retrieve the first and last date of a specified period. Functions that help you work on the opening and closing balances. Calculating Cumulative Sales You can use DAX time intelligence functions to create formulas for calculating cumulative sales. The following DAX functions can be used to calculate closing and opening balances − CLOSINGBALANCEMONTH (<expression>,<dates>, [<filter>]) − Evaluates the expression at the last date of the month in the current context. OPENINGBALANCEMONTH (<expression>,<dates>, [<filter>]) − Evaluates the expression at the first date of the month in the current context. CLOSINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) − Evaluates the expression at the last date of the quarter in the current context. OPENINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) − Evaluates the expression at the first date of the quarter, in the current context. CLOSINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>]) − Evaluates the expression at the last date of the year in the current context. OPENINGBALANCEYEAR (<expression>, <dates>, <filter>], [<year_end_date>]) − Evaluates the expression at the first date of the year in the current context. You can create the following calculated fields for the product inventory at a specified time by using the following DAX functions − Month Start Inventory Value: = OPENINGBALANCEMONTH ( SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey] ) Month End Inventory Value: = CLOSINGBALANCEMONTH ( SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey] ) Quarter Start Inventory Value: = OPENINGBALANCEQUARTER ( SUMX ProductInventory, (ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey] ) Quarter End Inventory Value: = CLOSINGBALANCEQUARTER ( SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey] ) Year Start Inventory Value: = OPENINGBALANCEYEAR ( SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey] ) Year End Inventory Value: = CLOSINGBALANCEYEAR ( SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey] ) Comparing Values across Different Time Periods The default time periods supported by DAX are months, quarters, and years. You can use the following DAX time intelligence functions to compare the sums across different time periods. PREVIOUSMONTH (<dates>) − Returns a table that contains a column of all the dates from the previous month, based on the first date in the dates column, in the current context. PREVIOUSQUARTER (<dates>) − Returns a table that contains a column of all the dates from the previous quarter, based on the first date in the dates column, in the current context. PREVIOUSYEAR (<dates>, <year_end_date>]) − Returns a table that contains a column of all dates from the previous year, given the last date in the dates column, in the current context. You can create the following calculated fields for calculating sum of sales in the West region at the specified time periods for comparison, by using the DAX functions − Previous Month Sales: = CALCULATE ( SUM (WestSales[SalesAmount]), PREVIOUSMONTH (DateTime [DateKey]) ) Previous Quarter Sales: = CALCULATE ( SUM (WestSales[SalesAmount]), PREVIOUSQUARTER (DateTime [DateKey]) ) Previous Year Sales: = CALCULATE ( SUM (WestSales[SalesAmount]), PREVIOUSYEAR (DateTime [DateKey]) ) Comparing Values Across Parallel Time Periods You can use the DAX time intelligence function PARALLELPERIOD to compare the sums across a period parallel to the specified time period. PARALLELPERIOD (<dates>, <number_of_intervals>, <interval>) This DAX function returns a table that contains a column of dates representing a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward or backward in time. You can create the following calculated field for calculating the previous year’s sales in West region − Previous Year Sales: = CALCULATE ( SUM (West_Sales[SalesAmount]), PARALLELPERIOD (DateTime[DateKey],-1,year) ) Calculating Running Totals You can use the following DAX time intelligence functions to calculate running totals or running sums. TOTALMTD (<expression>,<dates>, [<filter>]) − Evaluates the value of the expression for the month to date in the current context. TOTALQTD (<expression>,<dates>, <filter>]) − Evaluates the value of the expression for the dates in the quarter to date, in the current context. TOTALYTD (<expression>,<dates>, [<filter>], [<year_end_date>]) − Evaluates the year-to-date value of the expression in the current context. You can create the following calculated fields for calculating running sum of sales in the West region at specified time periods, by using the DAX functions − Month Running Sum: = TOTALMTD (SUM (West_Sales[SalesAmount]), DateTime[DateKey]) Quarter Running Sum: = TOTALQTD (SUM (WestSales[SalesAmount]), DateTime[DateKey]) Year Running Sum: = TOTALYTD (SUM (WestSales[SalesAmount]), DateTime[DateKey]) Calculating a Value over a Custom Date Range You can use DAX time intelligence functions to retrieve a custom set of dates, which you can use as an input to a DAX function that performs calculations, to create custom aggregates across time periods. DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>) − Returns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals. DATESBETWEEN (<dates>, <start_date>, ) − Returns a table that contains a column of dates that begins with the start_date and continues until the end_date. DATEADD (<dates>,<number_of_intervals>,<interval>) − Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context. FIRSTDATE (<dates>) − Returns the first date in the current context for the specified column of dates. LASTDATE (<dates>) − Returns the last date in the current context for the specified column of dates. You can create the following DAX formulas for calculating the sum of sales in the West region over a specified date range, by using the DAX functions − DAX Formula to calculate the sales for the 15 days prior to July 17, 2016. CALCULATE ( SUM (WestSales[SalesAmount]), DATESINPERIOD (DateTime[DateKey], DATE(2016,17,14), -15, day) ) DAX Formula to create a calculated field that calculates the first quarter 2016 sales. = CALCULATE (

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