Category: excel Dax

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 – Functions ”; Previous Next Most of the DAX functions have the same names and functionality as that of Excel functions. However, DAX functions have been modified to use DAX data types and to work with tables and columns. DAX has some additional functions that you will not find in Excel. These DAX functions are provided for specific purposes such as lookups based on relationships associated with the relational database aspects of the Data Model, the ability to iterate over a table to perform recursive calculations, to perform dynamic aggregation, and for calculations utilizing time intelligence. In this chapter, you will learn about the functions supported in the DAX language. For more information on the usage of these DAX functions, refer to the tutorial – DAX Functions in this tutorials library. What is a DAX Function? A DAX function is an in-built function provided in the DAX language to enable you to perform various actions on the data in the tables in your Data Model. As discussed earlier, DAX is used for data analysis and business intelligence purposes that require support to extract, assimilate, and derive insights from the data. The DAX functions that are based on the Data Model provide you with these utilities that make your job simpler, once you get a grasp on the DAX language and the usage of the DAX functions. Excel Functions vs. DAX Functions There are certain similarities between Excel functions that you are aware of and the DAX functions. However, there are certain differences too. You need to get a clarity on these, so that you can avoid making mistakes in the usage of DAX functions and in writing DAX formulas that include DAX functions. Similarities between Excel Functions and DAX Functions Many DAX functions have the same name and the same general behavior as Excel functions. DAX has lookup functions that are similar to the array and vector lookup functions in Excel. Differences between Excel Functions and DAX Functions DAX functions have been modified to take different types of inputs and some of the DAX functions might return a different data type. Hence, you need to understand the usage of these functions separately though they have the same name. In this tutorial, you will find every DAX function prefixed with DAX so as to avoid confusion with the Excel functions. You cannot use DAX functions in an Excel formula or use Excel formulas/functions in DAX, without the required modifications. Excel functions take a cell reference or a range of cells as reference. DAX functions never take a cell reference or a range of cells as reference, but instead take a column or table as reference. Excel date and time functions return an integer that represents a date as a serial number. DAX date and time functions return a datetime data type that is in DAX but not in Excel. Excel has no functions that return a table, but some functions can work with arrays. Many of the DAX functions can easily reference complete tables and columns to perform calculations and return a table or a column of values. This ability of DAX adds power to the Power Pivot, Power View and Power BI, where DAX is used. DAX lookup functions require that a relationship is established between the tables. Excel supports variant data type in a column of data, i.e. you can have data of different data types in a column. Whereas, DAX expects the data in a column of a table to be always of the same data type. If the data is not of the same data type, DAX changes the entire column to the data type that best accommodates all the values in the column. However, if the data is imported and this issue arises, DAX can flag an error. To learn about DAX data types and data type casting, refer to the chapter – DAX Syntax Reference. Types of DAX Functions DAX supports the following types of functions. DAX Table Valued Functions DAX Filter Functions DAX Aggregation Functions DAX Time Intelligence Functions DAX Date and Time Functions DAX Information Functions DAX Logical Functions DAX Math and Trig Functions DAX Other Functions DAX Parent and Child Functions DAX Statistical Functions DAX Text Functions In this section, you will learn about DAX functions at the functions category level. For details on the DAX Function Syntax and what the DAX function returns and does – refer to the DAX Functions tutorial in this tutorials library. DAX time intelligence functions and DAX filter functions are powerful and require a special mention. Refer to the chapters – Understanding DAX Time Intelligence and DAX Filter Functions for details. DAX Table Valued Functions Many DAX functions take tables as input or output tables or do both. These DAX functions are called DAX table valued functions. Because a table can have a single column, DAX table valued functions also take single columns as inputs. You have the following types of DAX table valued functions − DAX Aggregation functions DAX Filter functions DAX Time intelligence functions Understanding DAX table valued functions helps you in writing DAX formulas effectively. DAX Aggregation Functions DAX Aggregation functions aggregate any expression over the rows of a table and are useful in calculations. Following are some DAX Aggregation functions − Given below is the list of DAX Aggregation functions. ADDCOLUMNS (<table>, <name>, <expression>, [<name>, <expression>] …) AVERAGE (<column>) AVERAGEA (<column>) AVERAGEX (<table>, <expression>) COUNT (<column>) COUNTA (<column>) COUNTAX (<table>, <expression>) COUNTBLANK (<column>) COUNTROWS (<table>) COUNTX (<table>, <expression>) CROSSJOIN (<table1>, <table2>, [<table3>] …) DISTINCTCOUNT (<column>) GENERATE (<table1>, <table2>) GENERATEALL (<table1>, <table2>) MAX (<column>) MAXA (<column>) MAXX (<table>, <expression>) MIN (<column>) MINA (<column>) MINX (<table>, <expression>) PRODUCT (<column>) PRODUCTX (<table>, <expression>) ROW (<name>, <expression>, [<name>, <expression>] …) SELECTCOLUMNS (<table>, <name>, <scalar_expression>, [<name>, <scalar_expression>] …) SUM (<column>) SUMMARIZE (<table>, <groupBy_columnName>, [<groupBy_columnName>] …, [<name>, <expression>] …) SUMX (<table>, <expression>) TOPN (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]] …) DAX Filter Functions DAX Filter functions return a column, a table, or

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