Appendix B
DAX Function Reference
Data Analysis Epressions (DAX), is the language used by both PowerPivot and the Tabular mode of Analysis Services, and will be of great use during your data analysis. This appendix contains a function reference.
Updated Function Reference
Microsoft maintains an updated function reference at http://msdn.microsoft.com/en-us/library/ee634396.aspx.
Date and Time Functions
Date and time functions, as listed in Table B-1, allow for the manipulation of data stored in datetime formats.
Table B-1: Date and Time Functions
| Function | Description |
| DATE(<year>, <month>, <day>) | Returns the specified date in datetime format. |
| DATEVALUE(date_text) | Converts a date in the form of text to a date in datetime format. |
| DAY(<date>) | Returns the day of the month, a number from 1 to 31. |
| EDATE(<start_date>, <months>) | Returns the date that is the indicated number of months before or after the start date. Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. |
| EOMONTH(<start_date>, <months>) | Returns the date, in datetime format, of the last day of the month, before or after a specified number of months. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. |
| HOUR(<datetime>) | Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.). |
| MINUTE(<datetime>) | Returns the minute as a number from 0 to 59, given a date and time value. |
| MONTH(<datetime>) | Returns the month as a number from 1 (January) to 12 (December). |
| NOW() | Returns the current date and time in datetime format. |
| SECOND(<time>) | Returns the seconds of a time value, as a number from 0 to 59. |
| TIME(hour, minute, second) | Converts hours, minutes, and seconds given as numbers to a time in datetime format. |
| TIMEVALUE(time_text) | Converts a time in text format to a time in datetime format. |
| TODAY() | Returns the current date. |
| WEEKDAY(<date>, <return_type>) | Returns a number from 1 to 7 identifying the day of the week of a date. By default the day ranges from 1 (Sunday) to 7 (Saturday). |
| WEEKNUM(<date>, <return_type>) | Returns the week number for the given date and year according to the specified convention. The week number indicates where the week falls numerically within a year. |
| YEAR(<date>) | Returns the year of a date as a four digit integer in the range 1900–9999. |
| YEARFRAC(<start_date>, <end_date>, <basis>) | Calculates the fraction of the year represented by the number of whole days between two dates. Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. |
Filter Functions
Filter functions, shown in Table B-2, are applied within the Calculate function, and are used to select from a range of data.
Table B-2: Filter Functions
| Function | Description |
| ALL( <table> | <column1>, <column2>,…) | Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. |
| ALLEXCEPT(<table>, <column1>,<column2>,…) | Removes all context filters in the table except filters that have been applied to the specified columns. |
| ALLNOBLANKROW(<table> | <column>) | From the parent table of a relationship, returns all rows but the blank row, or all distinct values of a column but the blank row, and disregards any context filters that might exist. |
| ALLSELECTED([<table> | <column>]) | Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters. |
| CALCULATE(<expression>,<filter1>, <filter2>,…) | Evaluates an expression in a context that is modified by the specified filters. |
| CALCULATETABLE(<expression>, <filter1>, <filter2>,…) | Evaluates a table expression in a context modified by filters. |
| DISTINCT(<column>) | Returns a one-column table that contains the distinct values from the specified column. |
| EARLIER(<column>, <number>) | Returns the current value of the specified column in an outer evaluation pass of the mentioned column. |
| EARLIEST(<column>) | Returns the current value of the specified column in an outer evaluation pass of the mentioned column. EARLIEST is similar to EARLIER, but it lets you specify one additional level of recursion. |
| FILTER(<table>, <filter>) | Returns a table that represents a subset of another table or expression. |
| FILTERS(<column>) | Returns the values that are directly applied as filters to column. |
| HASONEFILTER(<column>) | Returns TRUE when the number of directly filtered values on column is one; otherwise returns FALSE. |
| HASONEVALUE(<column>) | Returns TRUE when the context for column has been filtered down to one distinct value only; otherwise, the function returns FALSE. |
| ISCROSSFILTERED(<column>) | Returns TRUE when column or another column in the same or related table is being filtered. |
| ISFILTERED(<column>) | Returns TRUE when column is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered, then the function returns FALSE. |
Information Functions
Information functions, as shown in Table B-3, return additional information, such as a data type.
Table B-3: Information Functions
| Function | Description |
| CONTAINS(<table>, <column1>, <value1>, <column2>, <value2>,…) | Returns TRUE if values for all referred columns exist, or are contained, in those columns; otherwise, the function returns FALSE. |
| ISBLANK(<value>) | Checks whether a value is blank, and returns TRUE or FALSE. |
| ISERROR(<value>) | Checks whether a value is an error, and returns TRUE or FALSE. |
| ISLOGICAL(<value>) | Checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE. |
| ISNONTEXT(<value>) | Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE. |
| ISNUMBER(<value>) | Checks whether a value is a number, and returns TRUE or FALSE. |
| ISTEXT(<value>) | Checks whether a value is text, and returns TRUE or FALSE. |
Lookup Functions
Lookup functions, as shown in Table B-4, are used to retrieve values from other tables for calculated columns, or can be used in calculated measures. Note that using related tables will improve performance dramatically, and using calculated measures will offer a processing and size improvement over calculated columns.
Table B-4: Lookup Functions
| Function | Description |
| LOOKUPVALUE( <result_column>, <search_column1>, <search_value1>[, <search_column2>, <search_value2>]…) | Returns the value in result_column for the row that meets all criteria specified by search_column and search_value. A replacement for VLOOKUP in Excel, LOOKUPVALUE is used only when relationships don’t exist. |
| RELATED(<column>) | Returns a related value from another table. |
| RELATEDTABLE(<table>) | Evaluates a table expression in a context modified by the given filters. Returns a set of values. |
| VALUES(<column>) | Returns a one-column table that contains the distinct values from the specified column including the Unknown member. |
Parent-Child Functions
Parent-child functions, shown in Table B-5, are used to implement parent-child hierarchies in DAX—these hierarchies are often called Ragged hierarchies.
Table B-5: Parent-Child Functions
| Function | Description |
| PATH(<ID_column>, <parent_column>) | Returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current. |
| PATHCONTAINS(<path>, <item>) | Returns TRUE if the specified item exists within the specified path. |
| PATHITEM(<path>, <position>[, <type>]) | Returns the item at the specified position from a string resulting from evaluation of a PATH function. Positions are counted from left to right. type is an optional enumeration that defines the data type of the result (Text or Integer). |
| PATHITEMREVERSE(<path>, <position>[, <type>]) | Returns the item at the specified position from a string resulting from evaluation of a PATH function. Positions are counted backwards from right to left. |
| PATHLENGTH(<path>) | Returns the number of parents to the specified item in a given PATH result, including self. |
Logical Functions
Logical functions, shown in Table B-6, are used for comparisons within a function.
Table B-6: Logical Functions
| Function | Description |
| AND(<logical1>, <logical2>) | Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise it returns FALSE. |
| FALSE() | Returns the logical value FALSE. |
| IF(<logical_test>,<value_if_true>, <value_if_false>) | Checks if a condition provided as the first argument is met. Returns one value if the condition is TRUE, and returns another value if the condition is FALSE. |
| IFERROR(<value>,<value_if_error>) | Evaluates an expression and returns a specified value if the expression returns an error; otherwise returns the value of the expression itself. |
| NOT(<logical>) | Changes FALSE to TRUE, or TRUE to FALSE. |
| OR(<logical1>, <logical2>) | Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if all arguments are FALSE. |
| SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>…, <else>) | Evaluates an expression against a list of values and returns one of multiple possible result expressions. else is the result if expression doesn't match any of the value arguments (optional). |
| TRUE() | Returns the logical value TRUE. |
Text Functions
Text functions, as shown in Table B-7, are used for formatting text.
Table B-7: Text Functions
| Function | Description |
| BLANK() | Returns a blank. |
| CONCATENATE(<text1>, <text2>) | Joins two text strings into one text string. |
| EXACT(<text1>,<text2>) | Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. |
| FIND(<find_text>, <within_text>, <start_num>, <NotFoundValue>) | Returns the starting position of one text string within another text string. FIND is case-sensitive. start_num and NotFoundValue are optional. |
| FIXED(<number>, <decimals>, <no_commas>) | Rounds a number to the specified number of decimals and returns the result as text. You can specify that the result be returned with or without commas. |
| FORMAT(<value>, <format_string>) | Converts a value to text according to the specified format. |
| LEFT(<text>, <num_chars>) | Returns the specified number of characters from the start of a text string. |
| LEN(<text>) | Returns the number of characters in a text string. |
| LOWER(<text>) | Converts all letters in a text string to lowercase. |
| MID(<text>, <start_num>, <num_chars>) | Returns a string of characters from the middle of a text string, given a starting position and length. |
| REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>) | REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. |
| REPT(<text>, <num_times>) | Repeats text a given number of times. |
| RIGHT(<text>, <num_chars>) | RIGHT returns the last character or characters in a text string, based on the number of characters you specify. |
| SEARCH(<search_text>, <within_text>, <start_num>, <NotFoundValue>) | Returns the number of the character at which a specific character or text string is first found, reading left to right. SEARCH is case-sensitive. start_num and NotFoundValue are optional. |
| SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>) | Replaces existing text with new text in a text string. |
| TRIM(<text>) | Removes all spaces from text except for single spaces between words. |
| UPPER (<text>) | Converts a text string to all uppercase letters. |
| VALUE(<text>) | Converts a text string that represents a number to a number. |
Predefined Numeric Formats for the FORMAT Function
Numeric formats, as shown in Table B-8, are used in the format function for formatting numbers.
Table B-8: Predefined Numeric Formats
| Format specification | Description |
| "General Number" | Displays numbers with no thousand separators. |
| "Currency" | Displays numbers with thousand separators, if appropriate; displays two digits to the right of the decimal separator. Output is based on system locale settings. |
| "Fixed" | Displays at least one digit to the left and two digits to the right of the decimal separator. |
| "Standard" | Displays numbers with thousand separators, at least one digit to the left and two digits to the right of the decimal separator. |
| "Percent" | Displays numbers multiplied by 100 with a percent sign (%) appended immediately to the right; always displays two digits to the right of the decimal separator. |
| "Scientific" | Uses standard scientific notation, providing two significant digits. |
| "Yes/No" | Displays No if number is 0; otherwise, displays Yes. |
| "True/False" | Displays False if number is 0; otherwise, displays True. |
| "On/Off" | Displays Off if number is 0; otherwise, displays On. |
Predefined Date and Time Formats for the FORMAT Function
Date and time formats, shown in Table B-9, are used in the FORMAT function for formatting numbers.
Table B-9: Predefined Date and Time Formats
| Format specification | Description |
| "General Date" | Displays a date and/or time. For example, 3/12/2008 11:07:31 AM. Date display is determined by your application's current culture value. |
| "Long Date" or "Medium Date" | Displays a date according to your current culture's long date format. For example, Wednesday, March 12, 2008. |
| "Short Date" | Displays a date using your current culture's short date format. For example, 3/12/2008. |
| "Long Time" or "Medium Time" | Displays a time using your current culture's long time format; typically includes hours, minutes, seconds. For example, 11:07:31 AM. |
| "Short Time" | Displays a time using your current culture's short time format. For example, 11:07 AM. |
Statistical Functions
DAX provides basic statistical functions such as means and counts. These are listed in Table B-10.
Table B-10: Statistical Functions
| Function | Description |
| AVERAGE(<column>) | Returns the average (arithmetic mean) of all the numbers in a column. |
| AVERAGEA(<column>) | Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values. |
| AVERAGEX(<table>, <expression>) | Calculates the average (arithmetic mean) of a set of expressions evaluated over a table |
| COUNT(<column>) | The COUNT function counts the number of cells in a column that contain numbers. |
| COUNTA(<column>) | The COUNTA function counts the number of cells in a column that are not empty. |
| COUNTAX(<table>, <expression>) | The COUNTAX function counts nonblank results when evaluating the result of an expression over a table. |
| COUNTBLANK(<column>) | Counts the number of blank cells in a column. |
| COUNTROWS(<table>) | The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression. |
| COUNTX(<table>, <expression>) | Counts the number of rows that contain a number or an expression that evaluates to a number, when evaluating an expression over a table. |
| DISTINCTCOUNT(<column>) | Counts the number of different cells in a column of numbers. |
| MAX(<column>) | Returns the largest numeric value in a column. |
| MAXA(<column>) | Returns the largest value in a column. Logical values and blanks are counted. |
| MAXX(<table>, <expression>) | Evaluates an expression for each row of a table and returns the largest numeric value. |
| MIN(<column>) | Returns the smallest numeric value in a column. Ignores logical values and text. |
| MINA(<column>) | Returns the smallest value in a column, including any logical values and numbers represented as text. |
| MINX(<table>, <expression>) | Returns the smallest numeric value that results from evaluating an expression for each row of a table. |
| RANK.EQ(<value>, <column>, <order>) | Returns the ranking of a number in a list of numbers. |
| RANKX(<table>, <expression>, <value>, <order>, <ties>) | Returns the ranking of a number in a list of numbers for each row in the table argument. |
| SUM(<column>) | Adds all the numbers in a column. |
| SUMX(<table>, <expression>) | Returns the sum of an expression evaluated for each row in a table. |
| TOPN(<n_value>, <table>, <orderBy_expression>, <order>) | Returns the top N rows of the specified table. |
Math and Trig Functions
DAX does not provide any of the trigonometry functions such as SIN and COS—you will need to use SIN tables to do these types of calculations. The functions DAX does provide are listed in Table B-11.
Table B-11: Math and Trig Functions
| Function | Description |
| ABS(<number>) | Returns the absolute value of a number. |
| CEILING(<number>, <significance>) | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
| CURRENCY(<value>) | Evaluates the argument and returns the result as currency data type. |
| DIVIDE(<numerator>, <denominator>, <alternateresult>) | Performs division and returns alternate result or BLANK() on division by 0. Alternateresult (optional) is the value returned when division by zero results in an error. |
| EXP(<number>) | Returns e raised to the power of a given number. The constant e equals 2.71828182845904, the base of the natural logarithm. |
| FACT(<number>) | Returns the factorial of a number, equal to the series 1*2*3*...* , ending in the given number. |
| FLOOR(<number>, <significance>) | Rounds a number down, toward zero, to the nearest multiple of significance. |
| INT(<number>) | Rounds a number down to the nearest integer. |
| LN(<number>) | Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). |
| LOG(<number>, <base>) | Returns the logarithm of a number to the base you specify. |
| LOG10(<number>) | Returns the base-10 logarithm of a number. |
| MOD(<number>, <divisor>) | Returns the remainder after a number is divided by a divisor. The result always has the same sign as the divisor. |
| MROUND(<number>, <multiple>) | Returns a number rounded to the desired multiple. |
| PI() | Returns the value of Pi, 3.14159265358979, accurate to 15 digits. |
| POWER(<number>, <power>) | Returns the result of a number raised to a power. |
| QUOTIENT(<numerator>, <denominator>) | Performs division and returns only the integer portion of the division result. Use this function when you want to discard the remainder of division. |
| RAND() | Returns a random number greater than or equal to 0 and less than 1, evenly distributed. The number that is returned changes each time the cell containing this function is recalculated. |
| RANDBETWEEN(<bottom>,<top>) | Returns a random number between the numbers you specify. |
| ROUND(<number>, <num_digits>) | Rounds a number to the specified number of digits. |
| ROUNDDOWN(<number>, <num_digits>) | Rounds a number down, toward 0 (zero). |
| ROUNDUP(<number>, <num_digits>) | Rounds a number up, away from 0 (zero). |
| SIGN(<number>) | Determines the sign of a number, the result of a calculation, or a value in a column. The function returns 1 if the number is positive, 0 (zero) if the number is zero, or -1 if the number is negative. |
| SQRT(<number>) | Returns the square root of a number. |
| TRUNC(<number>, <num_digits>) | Truncates a number to an integer by removing the decimal, or fractional, part of the number. |
Time Intelligence Functions
Time intelligence functions are provided for work with calendar months, and the functions are listed in Table B-12.
Table B-12: Time Intelligence Functions
| Function | Description |
| CLOSINGBALANCEMONTH(<expression>, <dates>, <filter>) | Evaluates the specified expression at the calendar end of the given month. The given month is calculated as the month of the latest date in the dates argument, after applying all filters. |
| CLOSINGBALANCEQUARTER(<expression>, <dates>, <filter>) | Evaluates the specified expression at the calendar end of the given quarter. The given quarter is calculated as the quarter of the latest date in the dates argument, after applying all filters. |
| CLOSINGBALANCEYEAR(<expression>, <dates>, <filter>) | Evaluates the specified expression at the calendar end of the given year. The given year is calculated as the year of the latest date in the dates argument, after applying all filters. |
| DATEADD(<date_column>,<number_of_intervals>, <interval>) | Returns a table that contains a column of dates, shifted either forward in time or back in time from the dates in the specified date column. |
| DATESBETWEEN(<column>, <start_date>, <end_date> | Returns a table of dates that can be found in the specified date column beginning with the start date and ending with the end date. |
| DATESINPERIOD(<date_column>, <start_date>,<number_of_intervals>, <intervals>) | Returns a table of dates that can be found in the specified date column beginning with the start date and continuing for the specified number of intervals. |
| DATESMTD(<dates>) | Returns a table that contains a column of the dates for the month to date. |
| DATESQTD(<date_column>) | Returns a table that contains a column of the dates for the quarter to date. |
| DATESYTD(<date_column> [,<YE_date>]) | Returns a table that contains a column of the dates for the year to date. The year_end_date (optional) is a literal string with a date that defines the year-end date. The default is December 31. |
| ENDOFMONTH(<date_column>) | Returns the last day of the month in the specified date column. |
| ENDOFQUARTER(<date_column>) | Returns the last day of the quarter in the specified date column. |
| ENDOFYEAR(<date_column>) | Returns the last day of the year in the specified date column. |
| FIRSTDATE(<date_column>) | Returns the first date in the current context for the specified date column. |
| FIRSTNONBLANK(<column>,<expression>) | Returns the first value in the column where the expression is not blank. |
| LASTDATE(<date_column>) | Returns the last date in the current context for the specified date column. |
| LASTNONBLANK (<date_column>, <expression>) | Returns the last value in the column where the expression is not blank. |
| NEXTDAY(<date_column>) | Returns the next day date from date_column. |
| NEXTMONTH(<date_column>) | Returns the set of dates in the next month from date_column. |
| NEXTQUARTER(<date_column>) | Returns the set of dates for the next quarter from date_column. |
| NEXTYEAR(<date_column>[,<YE_date>]) | Returns the set of dates for the next year from date_column. |
| OPENINGBALANCEMONTH(<expression>, <dates>, <filter>) | Evaluates the expression at the first date of the month, in the current context. |
| OPENINGBALANCEQUARTER(<expression>, <dates>, <filter>) | Evaluates the expression at the first date of the quarter, in the current context. |
| OPENINGBALANCEYEAR(<expression>, <dates>, <filter>) | Evaluates the expression at the first date of the year, in the current context. |
| PARALLELPERIOD(<dates>, <number_of_intervals>, <interval>) | Returns a table that contains a column of dates that represents 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 in time or back in time. |
| PREVIOUSDAY(<date_column>) | Returns a table that contains a column of all dates representing the day that is previous to the first date in the dates column, in the current context. |
| PREVIOUSMONTH(<date_column>) | Returns a table that contains a column of all dates from the previous month, based on the first date in the dates column, in the current context. |
| PREVIOUSQUARTER(<date_column>) | Returns a table that contains a column of all dates from the previous quarter, based on the first date in the dates column, in the current context. |
| PREVIOUSYEAR(<date_column>) | 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. |
| SAMEPERIODLASTYEAR(<dates>) | Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. |
| STARTOFMONTH(<date_column>) | Returns the first day of the month in the specified date column. |
| STARTOFQUARTER(<date_column>) | Returns the first day of the quarter in the specified date column. |
| STARTOFYEAR(<date_column>[,<YE_date>]) | Returns the first day of the year in the specified date column. |
| TotalMTD(<expression>, <dates>,<filter>) | Evaluates the specified expression for the interval that starts at the first day of the month and ends at the latest date in the specified dates column, after applying all filters. |
| TotalQTD(<expression>, <dates>, <filter>) | Evaluates the specified expression for the interval that starts at the first day of the quarter and ends at the latest date in the specified dates column, after applying all filters. |
| TotalYTD(<expression>, <dates>, <filter>) | Evaluates the specified expression for the interval that starts at the first day of the year and ends at the latest date in the specified dates column, after applying all filters. |
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.