Introduction
DAX functions are predefined functions in Power BI need to perform any calculation on given values in the formula. The value can be any column, text, logical value, number or a formula itself. Function must have at least one argument enclosed inside the parenthesis(). Function can have more than one argument.
You can check the below article to see how to use ‘SUM’ function
Key Facts about DAX Functions
Below are the key points about DAX functions that you must know
- DAX functions can be applied on a complete column or table but not on a particular value. You may apply filter if you want to operate a function on particular value.
- DAX function return a full table which can be utilized in other DAX equations that need a complete set of values. Be that as it may, you can't show this current table's substance.
- DAX functions give the adaptabilityto create a formula that is applied on a row-by-row basis. The formulas get applied as per the context of the values in each row.
- DAX functions have ‘Time intelligence function’ to calculate time/date range and periods. These functions are different from other ‘Date and time’ function.
Different type of DAX functions
Below is the list of most popular DAX function.
Date and Time Functions
The date time functions are used to calculate 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
Time Intelligence Functions
The time-intelligence functions are useful for evaluating 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
Information Functions
The information functions are used to give information on the data values contained in rows and columns. It evaluates the provided condition in a function for the value given and returns TRUE or FALSE. For example, the function ISERROR will return TRUE if the value contains an error.
- CONTAINS
- CUSTOMDATA
- IN Operator / CONTAINSROW function
- ISBLANK
- ISERROR
- ISEVEN
- ISINSCOPE
- ISLOGICAL
- ISNONTEXT
- ISNUMBER
- ISODD
- ISONORAFTER
- ISTEXT
- LOOKUPVALUE
- USERNAME
Logical Functions
The logical functions work logically and return TRUE or FALSE if the condition is met or not.
- AND
- FALSE
- IF
- IFERROR
- IN
- NOT
- OR
- SWITCH
- TRUE
Mathematical and Trigonometric Functions
The mathematical and trigonometric functions are used to perform mathematical functions on the given values. 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
Statistical Functions
These functions are also known as 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
Text Functions
The text functions in Power BI are just like 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
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
Other functions
Below function are not categorized but these are also useful.
- DATATABLE
- ERROR
- EXCEPT
- GENERATESERIES
- GROUPBY
- INTERSECT
- ISEMPTY
- ISSELECTEDMEASURE
- NATURALINNERJOIN
- NATURALLEFTOUTERJOIN
- SELECTEDSMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDSMEASURENAME
- SUMMARIZECOLUMNS
- Table Constructor
- TREATAS
- UNION
- VAR
Summary
Hope you are now aware with different type of DAX functions. In the next article I will show you how to use these functions in DAX formula. Thanks for reading.
Join the conversation! Your thoughts help the community grow.