DAX is a very powerful feature provided with Power BI from Microsoft. DAX stands for Data Analysis Expressions. As we can understand from the name DAX consists of the functions and expressions that are used to manipulate the data or the reports. These expressions help to perform analysis of the data and also do various types of calculations on the data. DAX makes use of various mathematical expressions such as functions, expressions, constants, and mathematical operators to give a feature-rich library to do simple to complex calculations.
Features of DAX
Some features of DAX are as follows:
- DAX is a functional language i.e. any mathematical expression is written in the form a function. A DAX function can contain numerical and nonnumerical values. Integers, rational numbers, etc. are considered numerical values while variables, symbols, etc. are considered nonnumerical values.
- DAX may also contain nested functions. If a DAX expression consists of nested functions, then the innermost function is evaluated first.
- Values of any datatype are supported by DAX expression. The conversion of one data type to the other is implicitly done by the DAX.
- DAX enables us to perform very complex calculations on the data.
Writing a DAX formula
A DAX formula can be written using the following syntax:
A = B(C[D])EF
Let us understand the syntax of DAX:
- A represents the variable that will store the result of the DAX expression.
- B represents the inbuilt DAX function which will be discussed later in this article.
- C represents the Table name on whose data we need to perform the calculation.
- D represents the columns of the table whose data ks is to be operated upon.
- E and F are optional parameters and represent mathematical operators and values respectively.
For example:
GrossRevenue = SUM(Sales(Revenue))*100000
This formula calculates the gross revenue by adding up the values in the revenue column of the Sales table and then multiplying the result by 100000.
DAX Functions
DAX has various types of inbuilt functions. Let us look at some of them.
Date and Time Functions
These functions are used to perform operations on the date and time. Some of these are:
- DATE: It gives the current date in the DateTime format.
- DATEDIFF: This function is used to get the difference in the number of days between two dates.
- DAY: Returns the day of a month as a number from 1 to 31.
- MONTH: Returns the month as a number from 1 to 12. 1 represents January and 12 represents December.
- YEAR: Returns the year from a given date. The returned value is in the range of 1900-9999.
- NOW: Returns the current date and time of the system.
- QUARTER: Returns the quarter number of a year from a given date.
Information Functions
These functions return important information about the value supplied to them. Some of them are:
- ISBLANK: Returns the value as True and False depending upon if the value is blank or not.
- ISEMPTY: Returns True if a table is empty and False if the table contains data.
- ISEVEN: Returns True if a number is even else False.
- ISODD: Returns True if a number is Odd, else False.
- ISLOGICAL: Returns True if a value is boolean, else False.
- ISTEXT: Returns True if the value is text, else False
- USERNAME: Returns the domain name and username from the login credentials used to log in to the system.
Logical Functions
Logical functions are used to return information about the values in an expression.
- AND: Return True if both the expressions supplied evaluate to True.
- IF: Checks whether the expression supplied to it evaluates to True or False and then executes the statements depending upon the result.
- NOT: Reverses the boolean value supplied to it i.e. True is converted to False and False is converted to True.
- OR: Returns true if any one of the expressions supplied to it evaluates to True.
Mathematical and Trigonometric Functions:
These functions are used to perform mathematical and trigonometric operations on the expression or value. All the trigonometric functions accept the value in radians. Some of these are:
- SIN: It gives the sine of the value supplied to it.
- COS: It returns the cosine of a given value.
- TAN: It returns the tangent of a given value.
- COSEC: It gives the cosecant of a value
- SEC: It returns the secant of a value.
- COT: It returns the cotangent of a value
- ROUND: It takes 2 arguments. First is the number to be rounded off and second is the number of digits to which the number should be rounded off.
Statistical Functions
These functions are used to perform statistical analysis on the data supplied. Some of these are:
- COMBIN: It is used to calculate the number of combinations for a given number without repetition.
- COMBINA: It gives the number of combinations for a given value with repetition.
- GEOMEAN: Returns the geometric mean of column values.
- MEDIAN: Returns the median value in a column
- PERMUT: This function gives the number of permutations for given values
- STDEV.P: It is used to calculate the standard deviation of the complete population.
- STDEV.S: It is used to calculate the standard deviation of the sample population.
Text Functions
These functions are used to perform operations on the text and strings. Some of these are:
- CONCATENATE: This function is used to join two strings or words.
- EXACT: It compares two strings and returns True if they are exactly the same.
- LEN: It is used to find the length of the string.
- LOWER: Converts a string to lowercase.
- UPPER: Converts a string to upper case.
- TRIM: This function removes all the whitespaces from the beginning and starting of a given string.
Table Manipulation Functions
These functions are used to perform manipulation on the tables such as filter, joins outer joins, etc. Some of these are:
- CROSSJOIN: Returns the cartesian product of two tables that contains rows from both tables.
- DISTINCT table: This function returns a table with unique values. Rows with duplicate values are eliminated from the table.
- GROUPBY: It is similar to the GROUPBY function in SQL
- INTERSECT: This function returns the intersection of two tables without removing the duplicates.