Statistical functions
Function
|
Description
|
AVERAGE()
|
Returns the average (arithmetic mean) of all the numbers in a column.
|
AVERAGEA()
|
Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values.
|
AVERAGEX(, )
Calculates the average (arithmetic mean) of a set of expressions evaluated over a table
|
COUNT()
|
The COUNT function counts the number of cells in a column that contain numbers.
|
COUNTA()
|
The COUNTA function counts the number of cells in a column that are not empty.
|
COUNTAX(, )
The COUNTAX function counts nonblank results when evaluating the result of an expression over a table.
|
COUNTBLANK()
|
Counts the number of blank cells in a column.
|
COUNTROWS()
The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.
|
COUNTX(, )
Counts the number of rows that contain a number or an expression that evaluates to a number, when evaluating an expression over a table.
|
MAX()
|
Returns the largest numeric value in a column.
|
MAXA()
|
Returns the largest value in a column. Logical values and blanks are counted.
|
MAXX(, )
Evaluates an expression for each row of a table and returns the largest numeric value.
|
MIN()
|
Returns the smallest numeric value in a column. Ignores logical values and text.
|
MINA()
|
Returns the smallest value in a column, including any logical values and numbers represented as text.
|
MINX(, < expression>)
Returns the smallest numeric value that results from evaluating an expression for each row of a table.
|
SUM()
|
Adds all the numbers in a column.
|
SUMX(, )
Returns the sum of an expression evaluated for each row in a table.
|
Text Functions
Function
|
Description
|
CODE()
|
Returns a numeric code for the first character in a text string, in the character set used by your computer.
|
CONCATENATE(, ,…)
|
The CONCATENATE function 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.
|
EXACT(,)
|
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.
|
FIND( |
Returns the starting position of one text string within another text string. FIND is case-sensitive.
|
FIXED(, , )
|
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.
|
LEFT(, )
|
Returns the specified number of characters from the start of a text string.
|
LEN()
|
Returns the number of characters in a text string.
|
LOWER()
|
Converts all letters in a text string to lowercase.
|
MID(, , )
|
Returns a string of characters from the middle of a text string, given a starting position and length.
|
REPLACE(, , , )
|
REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
|
REPT(, )
|
Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string
|
RIGHT(, )
|
RIGHT returns the last character or characters in a text string, based on the number of characters you specify.
|
SEARCH(, , [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.
|
SUBSTITUTE(, , , )
|
Replaces existing text with new text in a text string.
|
TRIM()
|
Removes all spaces from text except for single spaces between words.
|
UPPER ()
|
Converts a text string to all uppercase letters
|
VALUE()
|
Converts a text string that represents a number to a number.
|
FORMAT(, )
|
Converts a value to text according to the specified format.
|
Time Intelligence Functions
Function
|
Description
|
CLOSINGBALANCEMONTH(,,)
|
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(,,)
|
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(,,)
|
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.
|
DATESINPERIOD(,,,)
|
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.
|
DATESBETWEEN(,,
|
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.
|
DATEADD(,,)
|
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.
|
FIRSTDATE ()
|
Returns the first date in the current context for the specified Date_Column.
|
LASTDATE ()
|
Returns the last date in the current context for the specified Date_Column.
|
LASTNONBLANK (,)
|
?
|
STARTOFMONTH ()
|
Returns the first day of the month in the specified date column.
|
STARTOFQUARTER ()
|
Returns the first day of the quarter in the specified date column.
|
STARTOFYEAR([,])
|
Returns the first day of the year in the specified date column.
|
ENDOFMONTH()
|
Returns the last day of the month in the specified date column.
|
ENDOFQUARTER()
|
Returns the last day of the quarter in the specified date column.
|
ENDOFYEAR()
|
Returns the last day of the year in the specified date column.
|
PARALLELPERIOD(,,)
|
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.
|
PREVIOUSDAY()
|
Returns the previous day date from date_column.
|
PREVIOUSMONTH()
|
Returns the set of dates in the previous month from date_column.
|
PREVIOUSQUARTER()
|
Returns the set of dates in the previous quarter from date_column.
|
PREVIOUSYEAR()
|
Returns the set of dates in the previous year from date_column.
|
NEXTDAY()
|
Returns the next day date from date_column.
|
NEXTMONTH()
|
Returns the set of dates in the next month from date_column.
|
NEXTQUARTER ()
|
Returns the set of dates for the next quarter from date_column
|
NEXTYEAR([,])
|
Returns the set of dates for the next year from date_column.
|
DATESMTD()
|
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.
|
DATESQTD ()
|
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.
|
DATESYTD ( [,])
|
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.
|
SAMEPERIODLASTYEAR() - ?
|
?
|
OPENINGBALANCEMONTH(,,)
|
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.
|
OPENINGBALANCEQUARTER(,,)
|
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.
|
OPENINGBALANCEYEAR(,,)
|
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.
|
TotalMTD(,,)
|
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(,,)
|
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(,,)
|
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.
|
Справочник по языку выражений анализа данных (DAX)
SQL Server 2008 R2
Язык выражений анализа данных (DAX) — это новый язык формул, который можно использовать в книгах PowerPivot для Excel для создания пользовательских мер и вычисляемых столбцов в пределах хранящегося в памяти экземпляра служб Analysis Services. Язык DAX имеет синтаксис, весьма схожий с синтаксисом формул Excel, но DAX работает со столбцами и таблицами, а не с произвольными значениями или массивами. Кроме того, в DAX предусмотрен набор сложных функций логики операций со временем. В этом разделе приводятся сведения о синтаксисе и использовании языка DAX.
| | | | | | | |
