Introduction to DAX functions in Power BI 🪛
Using DAX functions in your reports will let you use datasets to the fullest, and discover new ways to calculate data values and come up with new insights. In this article, I will focus on DAX functions, which are predefined formulas that perform calculations on values provided in its arguments. The arguments should be in a particular order and can be a column reference, numbers, text, constants, another formula or function, or a logical value such as TRUE or FALSE.
What is DAX?
DAX stands for Data Analysis Expression such expressions or formulas that are used for data analysis and calculations. It is used in Analysis Services, Power BI, and Power Pivot in Excel.
Before listing any DAX function, it’s important to know the use cases of functions, here are some points to consider:
- A DAX function always references a column or a table. If a particular value is targeted you should use filters.
- DAX have many functions that return a table, rather than a value. The table is not viewed in reporting, but is used to provide input to other functions.
- DAX functions include a variety of time intelligence functions. These functions let you define or select date ranges, and perform dynamic calculations based on these dates or range.
Types of DAX Functions:
- Date and Time Functions
The date time functions do calculations on the date and time values. The data type of these values is always datetime data type. Here are some of the important functions you should know about:
- DATE
- DATEVALUE
- DAY
- NOW
- HOUR
- CALENDAR
- MONTH
- TIME
- TIMEVALUE
- TODAY
- WEEKDAY
- WEEKNUM
- YEAR
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.
- 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.
- 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 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
