Types of DAX functions
1. Date and Time Functions
The date time functions carry out calculations on the
date and time values. The data type of these values is always datetime data
type.
CALENDAR
CALENDARAUTO
DATE
DATEDIFF
DATEVALUE
DAY
EOMONTH
HOUR
MINUTE
MONTH
NOW
SECOND
TIME
TIMEVALUE
TODAY
WEEKDAY
WEEKNUM
YEAR
YEARFRAC
2. Time Intelligence Functions
The time-intelligence functions are used to evaluate
values over a fixed period such as days, weeks, months, quarter, years, etc.
You can specify a time period using these functions and compare two scenarios
in your report.
CLOSINGBALANCEMONTH
CLOSINGBALANCEQUARTER
CLOSINGBALANCEYEAR
DATEADD
DATESBETWEEN
DATESINPERIOD
DATESMTD
DATESQTD
DATESYTD
ENDOFMONTH
ENDOFQUARTER
ENDOFYEAR
FIRSTDATE
FIRSTNONBLANK
LASTDATE
NEXTQUARTER
LASTNONBLANK
NEXTDAY
NEXTMONTH
3. Information Functions
The information functions are used to provide certain
information on the data values contained in rows and columns. It evaluates the
given condition in a function for the value given and return TRUE or FALSE. For
instance, the function ISERROR will return TRUE if the value evaluated contains
an error.
CONTAINS
CUSTOMDATA
IN Operator / CONTAINSROW function
ISBLANK
ISERROR
ISEVEN
ISINSCOPE
ISLOGICAL
ISNONTEXT
ISNUMBER
ISODD
ISONORAFTER
ISTEXT
LOOKUPVALUE
USERNAME
4. Logical Functions
The logical functions are used to evaluate an expression
or argument logically and return TRUE or FALSE if the condition is met or not.
AND
FALSE
IF
IFERROR
IN
NOT
OR
SWITCH
TRUE
5. Mathematical and
Trigonometric Functions
The mathematical and trig functions are used to perform
all sorts of mathematical functions on the referred values. Given below, is a
list of all the available math and trig DAX functions in Power BI.
ABS
ACOS
ACOSH
ASIN
ASINH
ATAN
ATANH
CEILING
COMBIN
COMBINA
COS
COSH
CURRENCY
DEGREES
DIVIDE
EVEN
EXP
FACT
FLOOR
6. Statistical Functions
These functions carry out statistical and aggregation
functions on data values in a DAX expression in Power BI. The list of available
statistical functions is given below.
ADDCOLUMNS
APPROXIMATEDISTINCTCOUNT
AVERAGE
AVERAGEA
AVERAGEX
BETA.DIST
BETA.INV
CHISQ.INV
CHISQ.INV.RT
CONFIDENCE.NORM
CONFIDENCE.T
COUNT
COUNTA
COUNTAX
COUNTBLANK
COUNTROWS
COUNTX
CROSSJOIN
DISTINCTCOUNT
EXPON.DIST
GENERATE
GENERATEALL
GEOMEAN
GEOMEANX
MAX
MAXA
MAXX
MEDIAN
MEDIANX
MIN
MINA
MINX
7. Text Functions
The text functions in Power BI are very similar to the
string functions of Excel. These functions evaluate string values.
BLANK
CODE
COMBINEVALUES
CONCATENATE
CONCATENATEX
EXACT
FIND
FIXED
FORMAT
LEFT
LEN
LOWER
MID
REPLACE
REPT
RIGHT
SEARCH
SUBSTITUTE
TRIM
UNICHAR
UPPER
VALUE
8. Parent-Child functions
The parent and child functions are used for data values
that are a part of a parent-child hierarchy.
PATH
PATHCONTAINS
PATHITEM
PATHITEMREVERSE
PATHLENGTH
9. Other functions
There a bunch of functions that do not fit in any
particular category. These are also very useful functions.
DATATABLE
ERROR
EXCEPT
GENERATESERIES
GROUPBY
INTERSECT
ISEMPTY
ISSELECTEDMEASURE
NATURALINNERJOIN
NATURALLEFTOUTERJOIN
SELECTEDSMEASURE
SELECTEDMEASUREFORMATSTRING
SELECTEDSMEASURENAME
SUMMARIZECOLUMNS
Table Constructor
TREATAS
UNION
VAR
10. Table functions
The table functions in DAX formulas for Power BI are used
to apply operations and conditions on entire tables. The output of table
functions is used as inputs in other expressions or arguments in a DAX formula.
The results of these functions retain the relationships between columns of that
table.
FILTER
ALL
VALUES
DISTINCT
RELATEDTABLE