Learn About DAX Functions

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
  1. 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.
  2. 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.
  3. 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.
  4. 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,
  1. CALENDAR
  2. CALENDARAUTO
  3. DATE
  4. DATEDIFF
  5. DATEVALUE
  6. DAY
  7. EOMONTH
  8. HOUR
  9. MINUTE
  10. MONTH
  11. NOW
  12. SECOND
  13. TIME
  14. TIMEVALUE
  15. TODAY
  16. WEEKDAY
  17. WEEKNUM
  18. YEAR
  19. 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.
  1. CLOSINGBALANCEMONTH
  2. CLOSINGBALANCEQUARTER
  3. CLOSINGBALANCEYEAR
  4. DATEADD
  5. DATESBETWEEN
  6. DATESINPERIOD
  7. DATESMTD
  8. DATESQTD
  9. DATESYTD
  10. ENDOFMONTH
  11. ENDOFQUARTER
  12. ENDOFYEAR
  13. FIRSTDATE
  14. FIRSTNONBLANK
  15. LASTDATE
  16. NEXTQUARTER
  17. LASTNONBLANK
  18. NEXTDAY
  19. 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.
  1. CONTAINS
  2. CUSTOMDATA
  3. IN Operator / CONTAINSROW function
  4. ISBLANK
  5. ISERROR
  6. ISEVEN
  7. ISINSCOPE
  8. ISLOGICAL
  9. ISNONTEXT
  10. ISNUMBER
  11. ISODD
  12. ISONORAFTER
  13. ISTEXT
  14. LOOKUPVALUE
  15. USERNAME
Logical Functions
 
The logical functions work logically and return TRUE or FALSE if the condition is met or not.
  1. AND
  2. FALSE
  3. IF
  4. IFERROR
  5. IN
  6. NOT
  7. OR
  8. SWITCH
  9. 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.
  1. ABS
  2. ACOS
  3. ACOSH
  4. ASIN
  5. ASINH
  6. ATAN
  7. ATANH
  8. CEILING
  9. COMBIN
  10. COMBINA
  11. COS
  12. COSH
  13. CURRENCY
  14. DEGREES
  15. DIVIDE
  16. EVEN
  17. EXP
  18. FACT
  19. 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.
  1. ADDCOLUMNS
  2. APPROXIMATEDISTINCTCOUNT
  3. AVERAGE
  4. AVERAGEA
  5. AVERAGEX
  6. BETA.DIST
  7. BETA.INV
  8. CHISQ.INV
  9. CHISQ.INV.RT
  10. CONFIDENCE.NORM
  11. CONFIDENCE.T
  12. COUNT
  13. COUNTA
  14. COUNTAX
  15. COUNTBLANK
  16. COUNTROWS
  17. COUNTX
  18. CROSSJOIN
  19. DISTINCTCOUNT
  20. EXPON.DIST
  21. GENERATE
  22. GENERATEALL
  23. GEOMEAN
  24. GEOMEANX
  25. MAX
  26. MAXA
  27. MAXX
  28. MEDIAN
  29. MEDIANX
  30. MIN
  31. MINA
  32. MINX
Text Functions
 
The text functions in Power BI are just like to the string functions of Excel. These functions evaluate string values.
  1. BLANK
  2. CODE
  3. COMBINEVALUES
  4. CONCATENATE
  5. CONCATENATEX
  6. EXACT
  7. FIND
  8. FIXED
  9. FORMAT
  10. LEFT
  11. LEN
  12. LOWER
  13. MID
  14. REPLACE
  15. REPT
  16. RIGHT
  17. SEARCH
  18. SUBSTITUTE
  19. TRIM
  20. UNICHAR
  21. UPPER
  22. VALUE
Parent-Child functions
 
The parent and child functions are used for data values that are a part of a parent-child hierarchy.
  1. PATH
  2. PATHCONTAINS
  3. PATHITEM
  4. PATHITEMREVERSE
  5. PATHLENGTH
Other functions
 
Below function are not categorized but these are also useful.
  1. DATATABLE
  2. ERROR
  3. EXCEPT
  4. GENERATESERIES
  5. GROUPBY
  6. INTERSECT
  7. ISEMPTY
  8. ISSELECTEDMEASURE
  9. NATURALINNERJOIN
  10. NATURALLEFTOUTERJOIN
  11. SELECTEDSMEASURE
  12. SELECTEDMEASUREFORMATSTRING
  13. SELECTEDSMEASURENAME
  14. SUMMARIZECOLUMNS
  15. Table Constructor
  16. TREATAS
  17. UNION
  18. 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.


Similar Articles