Category: Big Data & Analytics

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