List of PowerPivot DAX functions (short)
User Rating: / 4
PoorBest 
Written by Vidas Matelis   
Friday, 13 November 2009 01:39

Download formated pdf document with DAX functions here...

Date and Time functions

DATE(<year>, <month>, <day>)
DATEVALUE(date_text)
DAY(<date>)
EDATE(<start_date>, <months>)
EOMONTH(<start_date>, <months>)
HOUR(<datetime>)
MINUTE(<datetime>)
MONTH(<datetime>)
NOW()
SECOND(<time>)
TIME(hour, minute, second)
TIMEVALUE(time_text)
TODAY()
WEEKDAY(<date>, <return_type>)
WEEKNUM(<date>, <return_type>)
YEAR(<date>)
YEARFRAC(<start_date>, <end_date>, <basis>)

Information functions

ISBLANK(<value>)
ISERROR(<value>)
ISLOGICAL(<value>)
ISNONTEXT(<value>)
ISNUMBER(<value>)
ISTEXT(<value>)

Filter and Value Functions

ALL(<table_or_column>)
ALLEXCEPT(<table>,column1>,<column2>,…)
BLANK()
CALCULATE(<expression>,<filter1>,<filter2>…)
CALCULATETABLE( <expression>, <filter1>, <filter2>,…)
DISTINCT(<column>)
EARLIER(<column>, <number>)
EARLIEST(<table_or_column>)
FILTER(<table>,<filter>)
RELATED(<column>)
RELATEDTABLE(<table>)
VALUES(<column>)
ALLNONBLANKROW(?)
FIRSTNONBLANK(<column>,<expression>)

Logical functions

AND(<logical1>,<logical2>,…)
FALSE()
IF(logical_test>,<value_if_true>, value_if_false)
IFERROR(value, value_if_error)
NOT(<logical>)
TRUE()
OR(<logical1>,<logical2>,…)

Math and Trig Functions

ABS(<number>)
CEILING(<number>, <significance>)
EXP(<number>)
FACT(<number>)
FLOOR(<number>, <significance>)
INT(<number>)
LN(<number>)
LOG(<number>,<base>)
LOG10(<number>)
MOD(<number>, <divisor>)
MROUND(<number>, <multiple>)
PI()
POWER(<number>, <power>)
QUOTIENT(<numerator>, <denominator>)
ROUND(<number>, <num_digits>)
ROUNDDOWN(<number>, <num_digits>)
ROUNDUP(<number>, <num_digits>)
SIGN(<number>)
SQRT(<number>)
TRUNC(<number>,<num_digits>)
[vb] RAND()
[vb] RANDBETWEEN(<bottom>,<top>)

Statistical functions

AVERAGE(<column>)
AVERAGEA(<column>)
AVERAGEX(<table>, <expression>)
COUNT(<column>)
COUNTA(<column>)
COUNTAX(<table>, <expression>)
COUNTBLANK(<column>)
COUNTROWS(<table>)
COUNTX(<table>, <expression>)
MAX(<column>)
MAXA(<column>)
MAXX(<table>, <expression>)
MIN(<column>)
MINA(<column>)
MINX(<table>, < expression>)
SUM(<column>)
SUMX(<table>, <expression>)

Text Functions

CODE(<text>)
CONCATENATE(<text1>, <text2>,…)
EXACT(<text1>,<text2>)
FIND(<find_text, within_text, start_num)
FIXED(<number>, <decimals>, <no_commas>)
LEFT(<text>, <num_chars>)
LEN(<text>)
LOWER(<text>)
MID(<text>, <start_num>, <num_chars>)
REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
REPT(<text>, <num_times>)
RIGHT(<text>, <num_chars>)
SEARCH(<search_text>, <within_text>, [start_num])
SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)
TRIM(<text>)
UPPER (<text>)
VALUE(<text>)
FORMAT(<value>, <format_string>)

Time Intelligence Functions

CLOSINGBALANCEMONTH(<expression>,<dates>,<filter>)
CLOSINGBALANCEQUARTER(<expression>,<dates>,<filter>)
CLOSINGBALANCEYEAR(<expression>,<dates>,<filter>)
DATESINPERIOD(<date_column>,<start_date>,<number_of_intervals>,<intervals>)
DATESBETWEEN(<column>,<start_date>,<end_date>
DATEADD(<date_column>,<number_of_intervals>,<interval>)
FIRSTDATE (<datecolumn>)
LASTDATE (<datecolumn>)
LASTNONBLANK (<datecolumn>,<expression>)
STARTOFMONTH (<date_column>)
STARTOFQUARTER (<date_column>)
STARTOFYEAR(<date_column>[,<YE_date>])
ENDOFMONTH(<date_column>)
ENDOFQUARTER(<date_column>)
ENDOFYEAR(<date_column>)
PARALLELPERIOD(<date_column>,<number_of_intervals>,<intervals>)
PREVIOUSDAY(<date_column>)
PREVIOUSMONTH(<date_column>)
PREVIOUSQUARTER(<date_column>)
PREVIOUSYEAR(<date_column>)
NEXTDAY(<date_column>)
NEXTMONTH(<date_column>)
NEXTQUARTER (<date_column>)
NEXTYEAR(<date_column>[,<YE_date>])
DATESMTD(<date_column>)
DATESQTD (<date_column>)
DATESYTD (<date_column> [,<YE_date>])
SAMEPERIODLASTYEAR() - ?
OPENINGBALANCEMONTH(<expression>,<dates>,<filter>)
OPENINGBALANCEQUARTER(<expression>,<dates>,<filter>)
OPENINGBALANCEYEAR(<expression>,<dates>,<filter>)
TotalMTD(<expression>,<dates>,<filter>)
TotalQTD(<expression>,<dates>,<filter>)
TotalYTD(<expression>,<dates>,<filter>)
  Tags: dax, cheat-sheet, excel
Comments (1)
1 Monday, 12 July 2010 04:28
James Liang
Thanks a lot. it's so helpful.
 

Varigence Vivid