Category: excel Dax

Conditional Values and Testing for Errors ”; Previous Next You can use DAX functions to test the values in the data that result in different values based on a condition. For e.g., you can test the yearly sales amount and based on the result, label resellers either as Preferred or Value. You can also use DAX functions for checking the range or the type of values, to prevent unexpected data errors from breaking calculations. Creating a Value Based on a Condition You can use nested IF conditions to test values and generate new values conditionally. Following DAX functions are useful for conditional processing and conditional values − IF (<logical_test>,<value_if_true>, [<value_if_false>]) − Checks if a condition is met. Returns one value if the condition is TRUE and returns another value if the condition is FALSE. Value_if_false is optional, and if omitted and the condition is FALSE, the function returns BLANK (). OR (<logical1>,<logical2>) − Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if both arguments are FALSE. CONCATENATE (<text1>, <text2>) − Joins two text strings into one text string. The joined items can be text, numbers, or Boolean values represented as text or a combination of those items. You can also use a column reference, if the column contains appropriate values. Testing for Errors within a DAX Formula In DAX, you cannot have valid values in one row of a calculated column and invalid values in another row. That is, if there is an error in any part of a calculated column, the entire column is flagged with an error and you must correct the DAX formula to remove the errors that result in invalid values. Some common errors in DAX formulas are − Division by zero. Argument to a function is blank while the expected argument is numeric value. You can use a combination of logical and information functions to test for errors and always return valid values to avoid returning errors in a calculated column. Following DAX functions help you in this. ISBLANK (<value>) − Checks whether a value is blank and returns TRUE or FALSE. IFERROR (value, value_if_error) − Returns value_if_error if the expression in the first argument results in an error. Otherwise, returns the value of the expression itself. Both the return value of the expression and value_if_error must be of same data type. Otherwise, you will get an error. Print Page Previous Next Advertisements ”;

Excel DAX – Performing Complex Calculations ”; Previous Next DAX formulas can perform complex calculations that involve custom aggregations, filtering, and the use of conditional values. You can do the following with DAX − Create custom calculations for a PivotTable. Apply a filter to a DAX formula. Remove filters selectively to create a dynamic ratio. Use a value from an outer loop. Creating Custom Calculations for a PivotTable DAX functions CALCULATE and CALCULATETABLE are powerful and flexible. They are useful for defining calculated fields. These DAX functions enable you to change the context in which the calculation will be performed. You can also customize the type of aggregation or mathematical operation to perform. CALCULATE Function CALCULATE (<expression>, [<filter1>], [<filter2>]…) CALCULATE function evaluates the given expression in a context that is modified by zero or more specified filters. If your data has been filtered, the CALCULATE function changes the context in which the data is filtered and evaluates the expression in the new context that you specify by the filters. That means, any existing filters on the specified column are removed and the filter used in the filter argument is applied instead. Example Suppose you want to display the percentage of medals sport-wise filtered by Country names. Your calculation should get the percentage value overriding the filter that you apply on Country in the PivotTable. Define a calculated field – Percentage of Medal Count as shown in the following screenshot. With this DAX formula, all the rows in the Results table are taken into account in the CALCULATE function with the filter containing the ALL function. This way, you have the total count in the denominator. Your PivotTable will be as shown in the following screenshot. In the above screenshot, Country is filtered to USA and Top 18 values are displayed in the PivotTable. Next, you can dynamically filter values in the PivotTable. However, the calculations will be correct by the custom DAX formula that you used. The CALCULATETABLE function takes a table of values and performs the same action as that of CALCULATE function. Filtering Data in Formulas You can create filters within DAX formulas, to select the values from the source data for use in calculations. You can do this by defining a filter expression and using it along with the table that is an input to the DAX formula. The filter expression enables you to obtain a subset of the source data. The filter is applied dynamically each time that you update the results of the DAX formula, depending on the current context of your data and you can be assured of accurate and expected results. The filter expression typically contains a DAX filter function that returns only selected rows of the table, which then can be used as an argument for another DAX function that you are using for data aggregation. Example The following screenshot shows the definition of a calculated field that gives medal count only for summer sports. With this calculated field, the PivotTable looks as shown in the following screenshot. As you can observe, the values in the PivotTable on the right side with the new calculated field match with those that are in the PivotTable on the left side with the filter on Season field applied explicitly. Note − DAX filter and value functions return a table, but never return the table or rows directly to the Data Model and hence are always embedded in another DAX function. For details on these DAX functions, refer to the chapter – DAX Filter Functions. Adding and Removing Filters Dynamically DAX Formulas that you use in a PivotTable can be affected by the PivotTable context. However, you can selectively change the context by adding or removing filters. You can use the DAX functions ALL and ALLEXCEPT to dynamically select the rows irrespective of the PivotTable context. Additionally, you can use the DAX functions DISTINCT and VALUES for returning distinct values. Using a Value from an Outer Loop You can use a value from a previous loop in creating a set of related calculations with DAX EARLIER function. This DAX function supports up to two levels of nested loops. Print Page Previous Next Advertisements ”;

Updating the Results of DAX Formulas ”; Previous Next DAX formulas are used in calculations involving large data, including data from external data sources. The data can be subjected to changes from time to time as the DAX calculations are meant for live data as well. The results of DAX formulas need to get updated on two occasions − Data Refresh − When the data is refreshed. Recalculation − When there are changes in the DAX formula. Understanding Data Refresh vs. Recalculation Data refresh and recalculation are two separate but related operations. Data refresh is the process of updating the data in the Data Model in your workbook obtaining up-to-date data from external data sources. Recalculation is the process of updating all the columns, tables, and PivotTables in your workbook that contain DAX formulas, to reflect the changes in the underlying data that result from the changes to DAX formulas themselves. You should not save or publish the workbook until the DAX formulas in it have been recalculated. Different Ways to Update Data in Data Model Power Pivot does not automatically detect changes in external data sources. You can refresh data manually from the Power Pivot window at intervals that you can specify. You can schedule an automatic data refresh from external sources, if you have published the workbook to a SharePoint site. For details on these, refer to the chapter – Updating Data in Data Model. Recalculation of DAX Formulas Recalculation of a DAX formula is an important task, because during recalculation, column dependencies are checked and you will be notified if a column has changed, if the data is invalid, or if an error has appeared in a DAX formula that used to work. Recalculation can affect performance in the following ways − For a calculated column, the result of DAX formula should always be recalculated for the entire column, whenever you change the DAX formula. For a calculated field, the result of DAX formula is not calculated until the calculated field is placed in the context of a PivotTable or a PivotChart. The DAX formula will be recalculated when you change any row or column heading that affects the filters on the data or when you manually refresh the PivotTable. In DAX, recalculating formulas can be done automatically or manually. To learn more about recalculation, refer to the chapter – Recalculating DAX Formulas. Print Page Previous Next Advertisements ”;

Excel DAX – Working with Text and Dates ”; Previous Next DAX can be used in the scenarios involving working along with text, extracting and composing date and time values or creating values based on a condition. You can do the following with DAX − Create a key column in a table by concatenation. Compose a date based on date parts extracted from a text date. Define a custom date format. Change data types using a formula. Convert real numbers to integers. Convert real numbers, integers, or dates to strings. Convert strings to real numbers or dates. Creating a Key Column by Concatenation Data Model in PowerPivot allows only a single key column. It does not support composite keys that you might find in the external data sources. Hence, if any composite keys exist in a table in the data source, you need to combine them into a single key column for the table in the Data Model. You can use the DAX function CONCATENATE to combine two columns into a single column in a table in the Data Model. DAX function CONCATENATE joins two text strings into one text string. The joined items can be text, numbers, or Boolean values represented as text or a combination of those items. You can also use a column reference if the column contains appropriate values. = CONCATENATE ([Column1], [Column2]) DAX CONCATENATE function accepts only two arguments. If any of the arguments is not of text data type, it will be converted to text. DAX CONCATENATE function returns the concatenated string. Date Based on Date Parts Extracted from a Text Date Data Model in Power Pivot supports a data type datetime for date and time values. The DAX functions that work on date and/or time values require the datetime data type for the arguments. If your data source contains dates of a different format, you need to first extract the date parts using a DAX formula and combine those parts to constitute a valid DAX datetime data type. You can use the following DAX functions to extract and compose dates − DATE − Returns the specified date in datetime format. DATEVALUE − Converts a date in the form of text to a date in datetime format. TIMEVALUE − Converts a time in text format to a time in datetime format. Defining a Custom Date Format Suppose the dates in your data source are not represented by a standard format. You can define a custom date format to ensure that the values are handled correctly. DAX FORMAT function enables you to convert a value to text according to the specified format. FORMAT (<value>, <format_string>) FORMAT function returns a string containing value formatted as defined by format_string. You can either use pre-defined Date and Time formats or you can create user-defined Date and Time formats for the argument format_string of the FORMAT function. Following are the predefined date and time format names. If you use strings other than these predefined strings, they will be interpreted as a custom date and time format. S. No. Format_String & Description 1 “General Date” Displays a date and/or time. For example, 2/10/2015 10:10:32 AM 2 “Long Date” or “Medium Date” Displays a date according to long date format. For example, Wednesday, March 07, 2016 3 “Short Date” Displays a date using short date format. For example, 2/03/2016 4 “Long Time” Displays a time using long time format. Typically includes hours, minutes and seconds. For example, 10:10:32 AM 5 “Medium Time” Displays a time in 12-hour format. For example, 09:30 PM 6 “Short Time” Displays a time in 24-hour format. For example, 14:15 Alternatively, you can use the characters in the following table to create user-defined date/time formats. S. No. Character & Description 1 : Time separator. Time separator. Separates hours, minutes, and seconds when time values are formatted. 2 / Date separator. Separates the day, month, and year when date values are formatted. 3 % Used to indicate that the following character should be read as a single-letter format without regard to any trailing letters. Also used to indicate that a single-letter format is read as a userdefined format. Following are the details of the various characters. %d − Displays the day as a number without a leading zero (e.g. 5). %dd − Displays the day as a number with a leading zero (e.g. 05). %ddd − Displays the day as an abbreviation (e.g. Sun). %dddd − Displays the day as a full name (e.g. Sunday). %M − Displays the month as a number without a leading zero (e.g. January is represented as 1). %MM − Displays the month as a number with a leading zero (e.g. January is represented as 01). %MMM − Displays the month as an abbreviation (e.g. January is represented as Jan). %MMMM − Displays the month as a full month name (e.g. January). %gg − Displays the period/era string (e.g. A.D.). %h − Displays the hour as a number without leading zeros using the 12-hour clock (e.g. 1:15:15 PM). Use %h if this is the only character in your user-defined numeric format. %hh − Displays the hour as a number with leading zeros using the 12-hour clock (e.g. 01:15:15 PM). %H − Displays the hour as a number without leading zeros using the 24-hour clock (e.g. 13:15:15, 1:15:15). Use %H if this is the only character in your user-defined numeric format. %HH − Displays the hour as a number with leading zeros using the 24-hour clock (e.g. 13:15:15, 1:15:15). %m − Displays the minute as a number without leading zeros (e.g. 2:1:15). Use %m if this is the only character in your user-defined numeric format. %mm − Displays the minute as a number with leading zeros (e.g. 2:01:15). %s − Displays the second as a number without leading zeros (e.g. 2:15:5). Use %s if this is the only character in your user-defined numeric format. %ss − Displays the second as a number with leading zeros (e.g. 2:15:05). %f − Displays

Excel DAX – Syntax ”; Previous Next As discussed earlier, DAX is a formula language comprising of operators, values, functions, and formulas. In this chapter, you will learn about DAX Syntax. DAX Syntax can be categorized as − Syntax for DAX Formulas DAX Naming Requirements DAX Special Values DAX Functions DAX Operators DAX Data Types Before you proceed to learning DAX Syntax, you have to understand the difference between Excel formulas and DAX formulas. Differences between Excel Formulas and DAX Formulas DAX formulas are similar to the Excel formulas and you can type them in the formula bar. However, there are some vital differences between the two. Excel Formula DAX Formula Excel formulas are typed in the formula bar in the Excel window. DAX formulas are typed in the formula bar in the Power Pivot window. In Excel formulas, you can reference individual cells or arrays for data. In DAX formulas, you can reference only complete tables or columns of data, i.e. references can be only to tables and fields in the tables. However, if at all you have to perform a calculation only on a part of the column data, you can do so with the DAX functions that filter and provide the required unique data values for calculation. Excel formulas support certain data types. DAX provides more data types than Excel does. Hence, DAX formulas can use the additional data types also. Excel does not support any implicit data conversions. DAX performs implicit data type conversions during calculations. Print Page Previous Next Advertisements ”;

Excel DAX – Calculated Fields / Measures ”; Previous Next A calculated field in a table in a Data Model is the field obtained by a DAX formula. In earlier versions of Power Pivot, the calculated field was termed as a measure. In Excel 2013, it was renamed as a calculated field. However, it is renamed back to measure in Excel 2016. If you refer to any documentation, you can observe a mix up of these two terms. Note that the terms calculated field and measure are synonymous. In this tutorial, we use the term calculated field. Understanding Calculated Fields A calculated field is a formula that is created specifically for use in a PivotTable (or PivotChart). You can create a calculated field based on standard aggregation functions, such as COUNT or SUM, or by defining your own DAX formula. Following is the difference between the calculated field and the calculated column − A calculated field can be used only in the VALUES area of a PivotTable. A calculated column with the calculated results can be used in ROWS, COLUMNS and FILTERS areas also. Saving Calculated Field The calculated field will be saved with its source table in the Data Model. It appears in the Power PivotTable or Power PivotChart Fields list as a field in the table. Using Calculated Field To use a calculated field, you have to select it from the Power PivotTable Fields list. The calculated field will get added to the VALUES area and the formula used for the calculated field will be evaluated. A result is created for each combination of row and column fields. Calculated Field – An Example Consider the following Data Model for Olympics data − As seen in the above screenshot, the Results table has a field Medal that contains the values – Gold, Silver, or Bronze for each of the rows containing Sport – Event – Country – Date combination. Suppose you want medal count for each country, then you can create a calculated field Medal Count with the following DAX formula − Medal Count := COUNTA([Medal]) Creating a Calculated Field in a Table To create the calculated field Medal Count in the Results table, do the following − Click the cell in the calculation area below the Medal column in the Results table. The cell will be highlighted. Type Medal Count:=COUNTA([Medal]) in the formula bar. Press Enter. As seen in the above screenshot, the calculated field appears in the selected cell, showing the value as 34,094. This number is the total number of rows in the Results table. Hence, it does not make much sense at the first look. As discussed earlier, the real use of a calculated field can be seen only by adding it to a Power PivotTable or a Power PivotChart. Using the Calculated Field in a Power PivotTable To use the calculated field to count the number of medals for each country, do the following − Click the PivotTable on the Ribbon in the Power Pivot window. Click the PivotTable in the dropdown list. Create PivotTable dialog box appears. Click the Existing Worksheet. Select where you want to place the PivotTable. An empty PivotTable will get created. Click the Results table in the PivotTable Fields list. Click the fields – Country and Medal count. As you can observe, Medal Count is added to VALUES area and Country is added to ROWS area. The PivotTable is created with the field Country values appearing in the rows. And for each row, the Medal Count value is calculated and displayed. That is the way, the calculated field evaluates the DAX formula used and displays the values. Add the field Sport from the Results table to ROWS area. As you can see in the above screenshot, Medal Count is calculated for each Country – Sport-wise and a Subtotal for the Country itself. This is how DAX supplements the Power features. Types of Calculated Fields There are two types of Calculated Fields – Implicit and Explicit. An implicit calculated field is created in the Power PivotTable Fields list pane. An explicit calculated field is created either in the table in the Power Pivot window, or from the PowerPivot Ribbon in the Excel window. Creating an Implicit Calculated Field An implicit calculated field can be created in two ways, both in the Power PivotTable Fields pane. Creating an Implicit Calculated Field in the PivotTable Fields List You can create the Count of Medal Field from the Medal field in the PivotTable Fields list as follows − Deselect the field Medal Count. Right-click on the field Medal. Click Add to Values in the dropdown list. Count of Medal appears in the Values area. Count of Medal column will be added to the PivotTable. Creating an Implicit Calculated Field in the VALUES Area You can create an implicit calculated field – % of Parent Row in the Values area to express the Medal count of each sport that a country has won as a percentage of the total number of Medals won by that Country. Click the down arrow in the Count of Medal box in VALUES area. Click the Value Field Settings in the dropdown list. Value Field Settings dialog box appears. Type % Medals in the Custom Name box. Click the Show Values As tab. Click the box under Show values as. Click the % of Parent Row Total. Click the Number Format button. Format Cells dialog box appears. Click Percentage. Type 0 in decimal places. Click OK. Click OK in the Value Field Settings dialog box. Select Do Not Show Subtotals. You created another implicit calculated field % Medals and as you can observe, for each Country, the percentage of Medals Sport-wise are displayed. Drawbacks of an Implicit Calculated Field Implicit calculated fields are easy to create. In fact, you have been creating them even in Excel PivotTables and Pivot Charts. But, they have the following drawbacks − They are volatile. That means, if you deselect

Excel DAX – Understanding DAX Functions ”; Previous Next In Excel 2013, DAX has 246 functions. You have already learnt about the different types of DAX functions in the chapter – DAX Functions. However, if you have to use a DAX function in a DAX formula, you need to understand the function in detail. You should know the syntax of the function, the parameter types, what the function returns, etc. If you are writing a DAX formula, it is suggested to use the DAX functions where applicable. For this, you can refer to the tutorial – DAX Functions in this tutorials library for an indepth understanding of where and how to use each of the 246 DAX functions. You can always go back and forth between this tutorial and DAX Functions tutorial to obtain mastery on DAX. In this chapter, you will understand how to read and interpret the DAX functions in the DAX Functions tutorial. DAX Function – Explanation Structure In the DAX functions tutorial, each DAX function is explained in a standard structure, comprising of the following sections − Description Syntax Parameters Return Value Remarks Example You will learn about each of these in the following sections. Description In the Description section, you will learn what the DAX function is about and where it can be used. Syntax In the Syntax section, you will learn the exact function name and the respective parameters. Parameters In the Parameters section, you will learn about each of the parameters of the specific DAX function, whether a parameter is an input or an output and if there are any options. As seen in the chapter – DAX Standard Parameters, only the standard parameter names will be used. Return Value In the Return Value section, you will learn about what value the DAX function will return and its data type. Remarks In the Remarks section, you will learn about any extra information that you need to know about the usage of the DAX function. Example The DAX function description will be ended with an example of the usage of the function. Print Page Previous Next Advertisements ”;

Excel DAX – Formula Errors ”; Previous Next You can get errors when you write DAX formulas with wrong syntax. Calculated fields and calculated columns can contain DAX functions that require a specific type of arguments. Arguments of DAX functions can be tables, columns, or other DAX functions (nested DAX functions). As DAX functions can return tables and columns, care should be taken to check that the right type of arguments are passed to the DAX functions. DAX formula errors can be either syntax errors or semantic errors. The errors can occur either at design time or at run time. In this chapter, you will learn about some common DAX errors, their causes, and how to fix those errors. DAX Error: Calculation Aborted The following error can occur when attempting to create (design-time) or use (run-time) a calculated field with a DAX time-intelligence function. In each case, a noncontiguous date range is being passed to the time intelligence function. “DAX Error: CALCULATION ABORTED: MdxScript (instance) (00, 0) Function ‘DATEADD’ only works with contiguous date selections.” Cause at Run-time This error can be displayed when a calculated field with a DAX time intelligence function is placed in the VALUES area of a PivotTable and date fields such as the month or the quarter are selected as slicers or filters before selecting a year. For example, if you have data of three years – 2014, 2015, and 2016 and you try to use only the month March without selecting the Year field, then the values are not contiguous data values and you will get an error. How to Fix the Error at Run-time? In the above example, First add Year as a slicer or a filter and select a year. Then, add Month or Quarter as a slicer or a filter. Then, select one or more months or quarters to slice or filter on for the year selected. Cause at Design-time DAX time intelligence functions require a date column specified for the date argument. The date column must have a contiguous range of dates. This error can be returned, if there is a date value in one or more rows in the date column that is not contiguous with the data values in the previous and successive rows. If you imported your table containing dates from a data source, remember that many organizations run special processes that scan tables in databases for invalid values and replace those with a particular value. That is, if an invalid date is found, it is assigned a particular date value that may not be contiguous with other data values in the column. How to Fix This Error at Design-time? Do the following to fix the error at design time − If your date table is imported from a data source, use Refresh in Power Pivot window to reimport any changes found at the source. Check the values in your date column to make sure they are in a contiguous order. If any value is found not to be in place, it will have to be corrected at the source and the date table will have to be refreshed. Create a separate date table and date column in your Data Model. Specify the new date column as the date argument in the formula causing the error. Date tables are easy to create and add to a Data Model. DAX Semantic Error – An Example The following DAX error is a semantic error − “A function ‘CALCULATE’ has been used in a true-false expression that is used as a table filter expression. This is not allowed.” Cause This error can appear when one or more filter expressions cannot be used in context of the calculated field or calculated column expression. In most of the cases, this error is caused by a filter expression specified as an argument to the DAX CALCULATE function. The CALCULATE function requires filters defined as a Boolean expression or a table expression. How to Fix Such Errors? You can fix such errors by using the DAX FILTER function to define filters as a table expression, which can then be used as an argument to the DAX CALCULATE function. Print Page Previous Next Advertisements ”;

Excel DAX – Operators ”; Previous Next DAX is a formula language comprising of functions, operators, and values that can be used in a formula or expression, to calculate and return one or more values. You can use DAX operators to compare values, perform arithmetic calculations, and concatenate strings. In this chapter, you will learn about DAX operators and how to use them. Types of DAX Operators DAX supports the following types of operators − DAX Arithmetic Operators DAX Comparison Operators DAX Text Concatenation Operator DAX Logical Operators DAX Operator Precedence Order You can have a DAX formula with many DAX operators combining several values or expressions. In such a case, the final result will depend on the order in which the operations are performed. DAX provides you with the default operator precedence order and also ways of overriding the default precedence order. DAX default operator precedence is listed in the following table. Precedence Order Operator(s) Operation 1 ^ Exponentiation 2 – Sign 3 * and / Multiplication and Division 4 ! NOT 5 + and – Addition and Subtraction 6 & Concatenation 7 =, <, >, <=, >= and <> Equal to, Less than, Greater than, Less than or equal to, Greater than or equal to and Not equal to DAX Expression Syntax You need to first understand the DAX expression syntax and how the expression evaluation is done with the operands and operators. All expressions always begin with an equal sign (=). The equal sign indicates that the succeeding characters constitute an expression. To the right of the equal sign, you will have the operands connected by the DAX operators. For example, = 5 + 4 > 5. = 5 * 6 – 3. Expressions are always read from left to right, and the calculations are done in that sequence, based on the DAX operator precedence given in the previous section. If the DAX operators have equal precedence value, they are evaluated from the left to right. For example, =5*6/10. Both * and / have same the precedent order. Hence, the expression is evaluated as 30/10 = 3. If the DAX operators in the expression have different precedence values, then they are evaluated in the precedence order from the left to right. = 5 + 4 > 7. Default precedence is + first and > next. Hence, the expression is calculated from the left to right. – 5 + 4 is calculated first resulting in 9 and then 9 > 5 is evaluated that results in TRUE. = 5 * 6 – 3. Default precedence is * first and – next. Hence, the expression is calculated from the left to right. – 5 * 6 is calculated first resulting in 30 and then 30 – 3 is calculated that results in 27. = 2 * 5 – 6 * 3. Default precedence is * first, * next and then -. Hence, the expression evaluates as 10 – 18 and then as -8. Note, that it is not 10 – 6 resulting in 4 and then 4*3 that is 12. Using Parentheses to Control DAX Calculation Order You can change the DAX default operator precedence order by using parentheses, grouping the operands and the operators to control the calculation sequence. For example, = 5 * 6 – 3 evaluates to 27 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 5 * (6 – 3), then 6 – 3 is calculated first resulting in 3 and then 5 * 3 is calculated which results in 15. = 2 * 5 – 6 * 3 evaluates to -8 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 2 * (5 – 6) * 3, then 5 – 6 is calculated first resulting in -1 and then 2 * (-1) * 3 is calculated which results in -6. As you can see, with the same operands and operators, different results are possible by the way you group them. Hence, when you use the DAX operators in the DAX formulas, you should pay attention to how the computation sequence is to be. Differences Between Excel and DAX Though DAX has similarities with Excel formulas, there are certain significant differences between the two. DAX is more powerful than Excel because of its underlying memory resident computation engine. DAX supports more data types than Excel. DAX provides additional advanced features of a relational database, Data Model, including richer support for date and time types. In some cases, the results of calculations or the behavior of functions in DAX may not be the same as in Excel. This is due to the differences in the following − Data type casting Data types Difference in Data Type Casting In DAX, when you have an expression =value1 operator value2, the two operands value1 and value2 should be of the same data type. If the data types are different, DAX will convert them first to a common data type implicitly. Refer to the chapter – DAX Syntax for details. For example, you have to compare two operands of different data types, say a number resulting from a formula, such as =[Amount] * 0.08 and an integer. The first number can be a decimal number with many decimal places, whereas the second number is an integer. Then DAX handles it as follows − First, DAX will convert both the operands to real numbers using the largest numeric format that can store both kinds of numbers. Next, DAX will compare the two real numbers. In contrast, Excel tries to compare values of different data types without first coercing them to a common data type. For this reason, you might find different results in DAX and in Excel for the same comparison expression. Difference in Data Types The operator precedence order in DAX and Excel is the same. However, the operator percent (%) and data ranges that

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