Data Analysis Expressions (DAX) are formulas or expressions used to examine and calculate data. These expressions consist of a collection of functions, operators, and constants that are combined into a single formula to achieve outcomes (value or values). In BI tools like Power BI, DAX equations are extremely useful since they help data analysts to get the most out of the data sets they have.
- DAX is a functional programming language, which means that its entire code is always a function. Conditional statements, nested functions, value references, and other things can be found in an executable DAX expression.
- Numeric and Non-numeric or Other data types are the two most common data types in DAX calculations. Integers, decimals, money, and other numeric data types are included in the numeric data type. Strings and binary things make up the non-numeric category.
- The evaluation of DAX expressions begins with the innermost function and ends with the outermost function. This necessitates the creation of a DAX formula.
DAX Functions
A DAX function is a formula that performs computations on values passed to it as parameters. A column reference, numbers, text, constants, another formula or function, or a logical value such as TRUE or FALSE are all examples of arguments in a function. Every function applies a certain operation on the values in an argument. In a DAX formula, you can use several arguments.
-
Date and Time Functions
Date and time data are calculated using the date and time functions. The datetime data type is always used for these items.
- 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 used to evaluate values over a specific time frame, such as days, weeks, months, quarters, and years. These services allow you to choose a time period 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 offer specific details about the data values in rows and columns. It evaluates the supplied condition in a function and returns TRUE or FALSE depending on the value. If the value evaluated contains an error, the function ISERROR, for example, will return TRUE.
- CONTAINS
- CUSTOMDATA
- IN Operator / CONTAINSROW function
- ISBLANK
- ISERROR
- ISEVEN
- ISINSCOPE
- ISLOGICAL
- ISNONTEXT
- ISNUMBER
- ISODD
- ISONORAFTER
- ISTEXT
- LOOKUPVALUE
- USERNAME
-
Logical Functions
The logical functions evaluate an expression or argument logically and return TRUE or FALSE depending on whether or not the condition is met.
- AND
- FALSE
- IF
- IFERROR
- IN
- NOT
- OR
- SWITCH
- TRUE
-
Mathematical and Trigonometric Functions
All kinds of mathematical functions are performed on the referred values using the mathematical and trig functions. A list of all the math and trig DAX functions available in Power BI is provided below.
- ABS
- ACOS
- ACOSH
- ASIN
- ASINH
- ATAN
- ATANH
- CEILING
- COMBIN
- COMBINA
- COS
- COSH
- CURRENCY
- DEGREES
- DIVIDE
- EVEN
- EXP
- FACT
- FLOOR
-
Statistical Functions
In Power BI, these functions perform statistical and aggregate operations on data values in a DAX expression. The following is a list of statistical functions that are available.
- ADDCOLUMNS
- APPROXIMATEDISTINCTCOUNT
- AVERAGE
- AVERAGEA
- AVERAGEX
- DIST
- INV
- INV
- INV.RT
- NORM
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- CROSSJOIN
- DISTINCTCOUNT
- DIST
- GENERATE
- GENERATEALL
- GEOMEAN
- GEOMEANX
- MAX
- MAXA
- MAXX
- MEDIAN
- MEDIANX
- MIN
- MINA
- MINX
-
Text Functions
Power BI’s text capabilities are extremely comparable to Excel’s string functions. These functions determine the value of a string.
- 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
For data values that are part of a parent-child hierarchy, the parent and child functions are utilised.
- PATH
- PATHCONTAINS
- PATHITEM
- PATHITEMREVERSE
- PATHLENGTH
-
Other functions
There are a number of functions that don’t fit neatly into any of the categories. These are also quite useful features.
- DATATABLE
- ERROR
- EXCEPT
- GENERATESERIES
- GROUPBY
- INTERSECT
- ISEMPTY
- ISSELECTEDMEASURE
- NATURALINNERJOIN
- NATURALLEFTOUTERJOIN
- SELECTEDSMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDSMEASURENAME
- SUMMARIZECOLUMNS
- Table Constructor
- TREATAS
- UNION
- VAR
-
Table functions
In DAX formulas for Power BI, table functions are used to apply operations and conditions to full tables. Table function outputs are utilized as arguments in DAX formulas or as inputs to other expressions. The associations between the columns of the table are preserved in the outcomes of these functions.
- FILTER
- ALL
- VALUES
- DISTINCT
- RELATEDTABLE
If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.
#analytics #data #business #artificialintelligence #machinelearning #startup #deeplearning #deeplearning #datascience #ai #growth #dataanalytics #india #datascientist #powerbi #dataanalysis #businessanalytics #businessanalyst #businessandmanagement #dataanalyst #businessanalysis #analyst #analysis #powerbideveloper #powerbidesktop #letsviz
