PowerPivot was tot op heden een niet aangesneden onderwerp op Exhelp.be. Bij deze brengen we daar verandering in, beginnende met een overzicht van alle DAX functies.
DAX (Data Analysis Expressions) is een formuletaal waarmee je aangepaste berekeningen kan definiëren in PowerPivot. DAX bevat een aantal functies die in Excel-formules worden gebruikt en extra functies die zijn ontworpen voor het werken met relationele gegevens en het uitvoeren van dynamische aggregatie.
In tegenstelling tot de ‘gewone’ Excel functies worden er in PowerPivot geen Nederlandstalige varianten voorzien voor DAX-functies. Deze bestaan dus enkel in het Engels.
Ben ik er ééntje vergeten? Laat het me weten!
| Type | DAX-Functie | Syntax | Omschrijving (EN) |
|---|---|---|---|
| Wiskundige en trigonometrische functies | ABS() | ABS(< number >) | Returns the absolute value of a number. |
| Statistische functies | ADDCOLUMNS() | ADDCOLUMNS(< table >, < name >, < expression >[, < name >, < expression >]…) | Adds calculated columns to the given table or table expression. |
| Filterfuncties | ALL() | ALL(< table_or_column >) | Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. |
| Filterfuncties | ALLEXCEPT() | ALLEXCEPT(< table >, column1 >, < column2 >, …) | Overrides all context filters in the table except filters that have been applied to the specified columns. |
| Filterfuncties | ALLNOBLANKROW() | ALLNOBLANKROW(< table >|< column >) | Returns all the rows, except for blank rows, in a table or column, and disregards any context filters that might exist. |
| Filterfuncties | ALLSELECTED() | ALLSELECTED([< tableName > | < columnName >]) | Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters. |
| Logische functies | AND() | AND(< logical1 >, < logical2 >, …) | Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE. |
| Statistische functies | AVERAGE() | AVERAGE(< column >) | Returns the average (arithmetic mean) of all the numbers in a column. |
| Statistische functies | AVERAGEA() | AVERAGEA(< column >) | Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values. |
| Statistische functies | AVERAGEX() | AVERAGEX(< table >, < expression >) | Calculates the average (arithmetic mean) of a set of expressions evaluated over a table |
| Filterfuncties | BLANK() | BLANK() | Returns a blank. |
| Filterfuncties | CALCULATE() | CALCULATE(< expression >, < filter1 >, < filter2 >…) | Evaluates an expression in a context that is modified by the specified filters. |
| Filterfuncties | CALCULATETABLE() | CALCULATETABLE( < expression >, < filter1 >, < filter2 >, …) | Evaluates a table expression in a context modified by filters. |
| Wiskundige en trigonometrische functies | CEILING() | CEILING(< number >, < significance >) | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
| Functies voor tijdintelligentie | CLOSINGBALANCEMONTH() | 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. |
| Functies voor tijdintelligentie | CLOSINGBALANCEQUARTER() | 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. |
| Functies voor tijdintelligentie | CLOSINGBALANCEYEAR() | 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. |
| Tekstfuncties | CODE() | CODE(< text >) | Returns a numeric code for the first character in a text string, in the character set used by your computer. |
| Tekstfuncties | CONCATENATE() | CONCATENATE(< text1 >, < text2 >, …) | Joins multiple 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. |
| Informatiefuncties | CONTAINS() | CONTAINS(< table >, < columnName >, < value >[, < columnName >, < value >]…) | Returns true if values for all referred columns exist, or are contained, in those columns; otherwise, the function returns false. |
| Statistische functies | COUNT() | COUNT(< column >) | Counts the number of cells in a column that contain numbers. |
| Statistische functies | COUNTA() | COUNTA(< column >) | Counts the number of cells in a column that are not empty. |
| Statistische functies | COUNTAX() | COUNTAX(< table >, < expression >) | Counts nonblank results when evaluating the result of an expression over a table. |
| Statistische functies | COUNTBLANK() | COUNTBLANK(< column >) | Counts the number of blank cells in a column. |
| Statistische functies | COUNTROWS() | COUNTROWS(< table >) | Counts the number of rows in the specified table, or in a table defined by an expression. |
| Statistische functies | COUNTX() | 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. |
| Statistische functies | CROSSJOIN() | CROSSJOIN(< table >, < table >[, < table >]…) | Returns a table that contains the Cartesian product of all rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables. |
| Wiskundige en trigonometrische functies | CURRENCY() | CURRENCY(< value >) | Evaluates the argument and returns the result as currency data type. |
| Datum- en tijdfuncties | DATE() | DATE(< year >, < month >, < day >) | Returns the specified date in datetime format. |
| Functies voor tijdintelligentie | DATEADD() | 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. |
| Functies voor tijdintelligentie | DATESBETWEEN() | 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. |
| Functies voor tijdintelligentie | DATESINPERIOD() | 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. |
| Functies voor tijdintelligentie | DATESMTD() | DATESMTD(< date_column >) | Returns the subset of dates, from date_column, for the interval that starts at the first day of the month and ends at the latest date in the specified dates column for the month that is the corresponding month of the latest date. |
| Functies voor tijdintelligentie | DATESQTD () | DATESQTD (< date_column >) | Returns the subset of dates, from date_column, for the interval that starts at the first day of the quarter and ends at the latest date in the specified dates column for the quarter that is the corresponding quarter of the latest date. |
| Functies voor tijdintelligentie | DATESYTD () | DATESYTD (< date_column > [, < YE_date >]) | Returns the subset of dates, from date_column, for the interval that starts the first day of the year and ends at the latest date in the specified dates column for the quarter that is the corresponding quarter of the latest date. |
| Datum- en tijdfuncties | DATEVALUE() | DATEVALUE(date_text) | Converts a date in the form of text to a date in datetime format |
| Datum- en tijdfuncties | DAY() | DAY(< date >) | Returns the day of the month, a number from 1 to 31. |
| Filterfuncties | DISTINCT() | DISTINCT(< column >) | Returns a one-column table that contains the distinct values from the specified column. |
| Statistische functies | DISTINCTCOUNT() | DISTINCTCOUNT(< column >) | Counts the number of different cells in a column of numbers. |
| Wiskundige en trigonometrische functies | DIVIDE() | DIVIDE(< numerator >, < denominator > [, < alternateresult >]) | Performs division and returns alternate result or BLANK() on division by 0. |
| Filterfuncties | EARLIER() | EARLIER(< column >, < number >) | Returns the current value of the specified column in an outer evaluation pass of the mentioned column. |
| Filterfuncties | EARLIEST() | EARLIEST(< table_or_column >) | Returns the current value of the specified column in an outer evaluation pass of the mentioned column |
| Datum- en tijdfuncties | EDATE() | 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 |
| Functies voor tijdintelligentie | ENDOFMONTH() | ENDOFMONTH(< date_column >) | Returns the last day of the month in the specified date column. |
| Functies voor tijdintelligentie | ENDOFQUARTER() | ENDOFQUARTER(< date_column >) | Returns the last day of the quarter in the specified date column. |
| Functies voor tijdintelligentie | ENDOFYEAR() | ENDOFYEAR(< date_column >) | Returns the last day of the year in the specified date column. |
| Datum- en tijdfuncties | EOMONTH() | 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. |
| Tekstfuncties | EXACT() | 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. You can use EXACT to test text being entered into a document. |
| Wiskundige en trigonometrische functies | EXP() | EXP(< number >) | Returns e raised to the power of a given number. The constant e equals 2.71828182845904, the base of the natural logarithm. |
| Wiskundige en trigonometrische functies | FACT() | FACT(< number >) | Returns the factorial of a number, equal to the series 1*2*3*...* , ending in the given number. |
| Logische functies | FALSE() | FALSE() | Returns the logical value FALSE. |
| Filterfuncties | FILTER() | FILTER(< table >, < filter >) | Returns a table that represents a subset of another table or expression. |
| Filterfuncties | FILTERS() | FILTERS(< columnName >) | Returns a table that represents a subset of another table or expression. |
| Tekstfuncties | FIND() | FIND(< find_text, within_text, start_num) | Returns the starting position of one text string within another text string. FIND is case-sensitive. |
| Functies voor tijdintelligentie | FIRSTDATE () | FIRSTDATE (< datecolumn >) | Returns the first date in the current context for the specified Date_Column. |
| Functies voor tijdintelligentie | FIRSTNONBLANK() | FIRSTNONBLANK(< column >, < expression >) | Returns the first non-blank values in column, filtered by expression. |
| Tekstfuncties | FIXED() | 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. |
| Wiskundige en trigonometrische functies | FLOOR() | FLOOR(< number >, < significance >) | Rounds a number down, toward zero, to the nearest multiple of significance. |
| Tekstfuncties | FORMAT() | FORMAT(< value >, < format_string >) | Converts a value to text according to the specified format. |
| Statistische functies | GENERATE() | GENERATE(< table1 >, < table2 >) | Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1. |
| Statistische functies | GENERATEALL() | GENERATEALL(< table1 >, < table2 >) | Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1. |
| Filterfuncties | HASONEFILTER() | HASONEFILTER(< columnName >) | Returns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE. |
| Filterfuncties | HASONEVALUE() | HASONEVALUE(< columnName >) | Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE. |
| Datum- en tijdfuncties | HOUR() | HOUR(< datetime >) | Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.). |
| Logische functies | IF() | 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. |
| Logische functies | IFERROR() | IFERROR(value, value_if_error) | Returns value_if_error if the first expression is an error and the value of the expression itself if otherwise. |
| Wiskundige en trigonometrische functies | INT() | INT(< number >) | Rounds a number down to the nearest integer. |
| Informatiefuncties | ISBLANK() | ISBLANK(< value >) | Checks whether a value is blank, and returns TRUE or FALSE. |
| Filterfuncties | ISCROSSFILTERED() | ISCROSSFILTERED(< columnName >) | Returns TRUE when columnName or another column in the same or related table is being filtered. |
| Informatiefuncties | ISERROR() | ISERROR(< value >) | Checks whether a value is an error, and returns TRUE or FALSE. |
| Filterfuncties | ISFILTERED() | ISFILTERED(< columnName >) | Returns TRUE when columnName 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. |
| Informatiefuncties | ISLOGICAL() | ISLOGICAL(< value >) | Checks whether a value is a logical value, (TRUE or FALSE), and returns TRUE or FALSE. |
| Informatiefuncties | ISNONTEXT() | ISNONTEXT(< value >) | Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE. |
| Informatiefuncties | ISNUMBER() | ISNUMBER(< value >) | Checks whether a value is a number, and returns TRUE or FALSE. |
| Wiskundige en trigonometrische functies | ISO.CEILING() | ISO.CEILING(< number >[, < significance >]) | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
| Informatiefuncties | ISTEXT() | ISTEXT(< value >) | Checks whether a value is text, and returns TRUE or FALSE. |
| Functies voor tijdintelligentie | LASTDATE () | LASTDATE (< datecolumn >) | Returns the last date in the current context for the specified Date_Column. |
| Functies voor tijdintelligentie | LASTNONBLANK () | LASTNONBLANK (< datecolumn >, < expression >) | Returns the last value in the column, column, filtered by the current context, where the expression is not blank. |
| Tekstfuncties | LEFT() | LEFT(< text >, < num_chars >) | Returns the specified number of characters from the start of a text string. |
| Tekstfuncties | LEN() | LEN(< text >) | Returns the number of characters in a text string. |
| Wiskundige en trigonometrische functies | LN() | LN(< number >) | Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). |
| Wiskundige en trigonometrische functies | LOG() | LOG(< number >, < base >) | Returns the logarithm of a number to the base you specify. |
| Wiskundige en trigonometrische functies | LOG10() | LOG10(< number >) | Returns the base-10 logarithm of a number. |
| Informatiefuncties | LOOKUPVALUE() | LOOKUPVALUE( < result_columnName >, < search_columnName >, < search_value >[, < search_columnName >, < search_value >]…) | Returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value. |
| Tekstfuncties | LOWER() | LOWER(< text >) | Converts all letters in a text string to lowercase. |
| Statistische functies | MAX() | MAX(< column >) | Returns the largest numeric value in a column. |
| Statistische functies | MAXA() | MAXA(< column >) | Returns the largest value in a column. Logical values and blanks are counted. |
| Statistische functies | MAXX() | MAXX(< table >, < expression >) | Evaluates an expression for each row of a table and returns the largest numeric value. |
| Tekstfuncties | MID() | MID(< text >, < start_num >, < num_chars >) | Returns a string of characters from the middle of a text string, given a starting position and length. |
| Statistische functies | MIN() | MIN(< column >) | Returns the smallest numeric value in a column. Ignores logical values and text. |
| Statistische functies | MINA() | MINA(< column >) | Returns the smallest value in a column, including any logical values and numbers represented as text. |
| Datum- en tijdfuncties | MINUTE() | MINUTE(< datetime >) | Returns the minute as a number from 0 to 59, given a date and time value. |
| Statistische functies | MINX() | MINX(< table >, < expression >) | Returns the smallest numeric value that results from evaluating an expression for each row of a table. |
| Wiskundige en trigonometrische functies | MOD() | MOD(< number >, < divisor >) | Returns the remainder after a number is divided by a divisor. The result always has the same sign as the divisor. |
| Datum- en tijdfuncties | MONTH() | MONTH(< datetime >) | Returns the month as a number from 1 (January) to 12 (December). |
| Wiskundige en trigonometrische functies | MROUND() | MROUND(< number >, < multiple >) | Returns a number rounded to the desired multiple. |
| Functies voor tijdintelligentie | NEXTDAY() | NEXTDAY(< date_column >) | Returns the next day date from date_column. |
| Functies voor tijdintelligentie | NEXTMONTH() | NEXTMONTH(< date_column >) | Returns the set of dates in the next month from date_column. |
| Functies voor tijdintelligentie | NEXTQUARTER () | NEXTQUARTER (< date_column >) | Returns the set of dates for the next quarter from date_column |
| Functies voor tijdintelligentie | NEXTYEAR() | NEXTYEAR(< date_column >[, < YE_date >]) | Returns the set of dates for the next year from date_column. |
| Logische functies | NOT() | NOT(< logical >) | Changes FALSE to TRUE, or TRUE to FALSE. |
| Datum- en tijdfuncties | NOW() | NOW() | Returns the current date and time in datetime format. |
| Functies voor tijdintelligentie | OPENINGBALANCEMONTH() | OPENINGBALANCEMONTH(< expression >, < dates >, < filter >) | Evaluates the specified expression at the calendar end of the month prior the given month. The given month is calculated as the month of the latest date in the dates argument, after applying all filters. |
| Functies voor tijdintelligentie | OPENINGBALANCEQUARTER() | OPENINGBALANCEQUARTER(< expression >, < dates >, < filter >) | Evaluates the specified expression at the calendar end of the quarter prior to the given quarter. The given quarter is calculated as the quarter of the latest date in the dates argument, after applying all filters. |
| Functies voor tijdintelligentie | OPENINGBALANCEYEAR() | OPENINGBALANCEYEAR(< expression >, < dates >, < filter >) | Evaluates the specified expression at the calendar end of the year prior to the given year. The given year is calculated as the year of the latest date in the dates argument, after applying all filters. |
| Logische functies | OR() | OR(< logical1 >, < logical2 >, …) | Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if all arguments are FALSE |
| Functies voor tijdintelligentie | PARALLELPERIOD() | PARALLELPERIOD(< date_column >, < number_of_intervals >, < intervals >) | This function moves the specified number of intervals and then returns all contiguous full months which contain any values after that shift. Gaps between the first and last dates are filled in, and months are also filled in. |
| Informatiefuncties | PATH() | PATH(< ID_columnName >, < parent_columnName >) | Returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current. |
| Informatiefuncties | PATHCONTAINS() | PATHCONTAINS(< path >, < item >) | Returns TRUE if the specified item exists within the specified path. |
| Informatiefuncties | PATHITEM() | PATHITEM(< path >, < position >[, < type >]) | Returns the item at the specified position from a PATH() like result, counting from left to right. |
| Informatiefuncties | PATHITEMREVERSE() | PATHITEMREVERSE(< path >, < position >[, < type >]) | Returns the item at position from a PATH() like function result, counting backwards from right to left. |
| Informatiefuncties | PATHLENGTH() | PATHLENGTH(< path >) | Returns the number of levels in a given PATH(), starting at current level until the oldest or top most parent level. |
| Wiskundige en trigonometrische functies | PI() | PI() | Returns the value of Pi, 3.14159265358979, accurate to 15 digits. |
| Wiskundige en trigonometrische functies | POWER() | POWER(< number >, < power >) | Returns the result of a number raised to a power. |
| Functies voor tijdintelligentie | PREVIOUSDAY() | PREVIOUSDAY(< date_column >) | Returns the previous day date from date_column. |
| Functies voor tijdintelligentie | PREVIOUSMONTH() | PREVIOUSMONTH(< date_column >) | Returns the set of dates in the previous month from date_column. |
| Functies voor tijdintelligentie | PREVIOUSQUARTER() | PREVIOUSQUARTER(< date_column >) | Returns the set of dates in the previous quarter from date_column. |
| Functies voor tijdintelligentie | PREVIOUSYEAR() | PREVIOUSYEAR(< date_column >) | Returns the set of dates in the previous year from date_column. |
| Wiskundige en trigonometrische functies | QUOTIENT() | 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. |
| Wiskundige en trigonometrische functies | RAND() | 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. |
| Wiskundige en trigonometrische functies | RANDBETWEEN() | RANDBETWEEN(< bottom >, < top >) | Returns a random number between the numbers you specify |
| Statistische functies | RANK.EQ() | RANK.EQ(< value >, < columnName >[, < order >]) | Returns the ranking of a number in a list of numbers. |
| Statistische functies | RANKX() | RANKX(< table >, < expression >[, < value >[, < order >[, < ties >]]][, < expression >[, < value >[, < order >[, < ties >]]]]…) | Returns the ranking of a number in a list of numbers for each row in the table argument. |
| Filterfuncties | RELATED() | RELATED(< column >) | Returns a related value from another table |
| Filterfuncties | RELATEDTABLE() | RELATEDTABLE(< table >) | Follows an existing relationship, in either direction, and returns a table that contains all matching rows from the specified table. |
| Tekstfuncties | REPLACE() | REPLACE(< old_text >, < start_num >, < num_chars >, < new_text >) | Replaces part of a text string, based on the number of characters you specify, with a different text string. |
| Tekstfuncties | REPT() | REPT(< text >, < num_times >) | Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string |
| Tekstfuncties | RIGHT() | RIGHT(< text >, < num_chars >) | Returns the last character or characters in a text string, based on the number of characters you specify. |
| Wiskundige en trigonometrische functies | ROUND() | ROUND(< number >, < num_digits >) | Rounds a number to the specified number of digits. |
| Wiskundige en trigonometrische functies | ROUNDDOWN() | ROUNDDOWN(< number >, < num_digits >) | Rounds a number down, toward zero. |
| Wiskundige en trigonometrische functies | ROUNDUP() | ROUNDUP(< number >, < num_digits >) | Rounds a number up, away from 0 (zero). |
| Statistische functies | ROW() | ROW(< name >, < expression >][, < name >, < expression >]…]) | Returns a table with a single row containing values that result from the expressions given to each column. |
| Functies voor tijdintelligentie | SAMEPERIODLASTYEAR() | 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. |
| Tekstfuncties | SEARCH() | SEARCH(< search_text >, < within_text >, [start_num]) | 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. |
| Datum- en tijdfuncties | SECOND() | SECOND(< time >) | Returns the seconds of a time value, as a number from 0 to 59 |
| Wiskundige en trigonometrische functies | SIGN() | 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. |
| Wiskundige en trigonometrische functies | SQRT() | SQRT(< number >) | Returns the square root of a number. |
| Functies voor tijdintelligentie | STARTOFMONTH () | STARTOFMONTH (< date_column >) | Returns the first day of the month in the specified date column. |
| Functies voor tijdintelligentie | STARTOFQUARTER () | STARTOFQUARTER (< date_column >) | Returns the first day of the quarter in the specified date column. |
| Functies voor tijdintelligentie | STARTOFYEAR() | STARTOFYEAR(< date_column >[, < YE_date >]) | Returns the first day of the year in the specified date column. |
| Statistische functies | STDEV.P() | STDEV.P(< ColumnName >) | Returns the standard deviation of the entire population. |
| Statistische functies | STDEV.S() | STDEV.S(< ColumnName >) | Returns the standard deviation of a sample population. |
| Statistische functies | STDEVX.P() | STDEVX.P(< table >, < expression >) | Returns the standard deviation of the entire population. |
| Statistische functies | STDEVX.S() | STDEVX.S(< table >, < expression >) | Returns the standard deviation of a sample population. |
| Tekstfuncties | SUBSTITUTE() | SUBSTITUTE(< text >, < old_text >, < new_text >, < instance_num >) | Replaces existing text with new text in a text string. |
| Wiskundige en trigonometrische functies | SUM() | SUM(< column >) | Adds all the numbers in a column. |
| Statistische functies | SUMMARIZE() | SUMMARIZE(< table >, < groupBy_columnName >[, < groupBy_columnName >]…[, < name >, < expression >]…) | Returns a summary table for the requested totals over a set of groups. |
| Wiskundige en trigonometrische functies | SUMX() | SUMX(< table >, < expression >) | Returns the sum of an expression evaluated for each row in a table. |
| Logische functies | SWITCH() | SWITCH(< expression >, < value >, < result >[, < value >, < result >]…[, < else >]) | Evaluates an expression against a list of values and returns one of multiple possible result expressions. |
| Datum- en tijdfuncties | TIME() | TIME(hour, minute, second) | Converts hours, minutes, and seconds given as numbers to a time in datetime format. |
| Datum- en tijdfuncties | TIMEVALUE() | TIMEVALUE(time_text) | Converts a time in text format to a time in datetime format. |
| Datum- en tijdfuncties | TODAY() | TODAY() | Returns the current date. |
| Statistische functies | TOPN() | TOPN(< n_value >, < table >, < orderBy_expression >, [< order >[, < orderBy_expression >, [< order >]]…]) | Returns the top N rows of the specified table. |
| Functies voor tijdintelligentie | TotalMTD() | 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. |
| Functies voor tijdintelligentie | TotalQTD() | 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. |
| Functies voor tijdintelligentie | TotalYTD() | 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. |
| Tekstfuncties | TRIM() | TRIM(< text >) | Removes all spaces from text except for single spaces between words. |
| Logische functies | TRUE() | TRUE() | Returns the logical value TRUE. |
| Wiskundige en trigonometrische functies | TRUNC() | TRUNC(< number >, < num_digits >) | Truncates a number to an integer by removing the decimal, or fractional, part of the number. |
| Tekstfuncties | UPPER () | UPPER (< text >) | Converts a text string to all uppercase letters |
| Tekstfuncties | VALUE() | VALUE(< text >) | Converts a text string that represents a number to a number. |
| Filterfuncties | VALUES() | VALUES(< column >) | Returns a one-column table that contains the distinct values from the specified column. This function is similar to DISTINCT function, but VALUES function can also return Unknown member |
| Statistische functies | VAR.P() | VAR.P(< columnName >) | Returns the variance of the entire population. |
| Statistische functies | VAR.S() | VAR.S(< columnName >) | Returns the variance of a sample population. |
| Statistische functies | VARX.P() | VARX.P(< table >, < expression >) | Returns the variance of the entire population. |
| Statistische functies | VARX.S() | VARX.S(< table >, < expression >) | Returns the variance of a sample population. |
| Datum- en tijdfuncties | WEEKDAY() | 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). |
| Datum- en tijdfuncties | WEEKNUM() | 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. |
| Datum- en tijdfuncties | YEAR() | YEAR(< date >) | Returns the year of a date as a four digit integer in the range 1900-9999. |
| Datum- en tijdfuncties | YEARFRAC() | 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. |