_edited_edited.png)
Data Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. DAX language is very useful as it allows Data Analysts to perform advanced calculations and discover a hidden pattern in an unstructured dataset. The complete code of an expression is always a function or nested function with conditional statements, value references, formulas, loops, etc.
DAX formulas are made up of 3 core components:
Syntax – Proper DAX syntax is made up of a variety of elements, some of which are common to all formulas. When trying to understand a DAX formula, it is often helpful to break down each of the elements into a language you think and speak every day. So, this formula includes the following syntax elements:
Total Profit = SUM(Orders[Profit])
Total Profit is the measure name.
The equals sign operator (=) indicates the beginning of the formula.
SUM adds up all of the numbers in the profit column.
Parentheses () surround an expression containing one or more arguments. All functions require at least one argument.
Orders is the table referenced.
Profit is a value passed to a function. The referenced column [Profit] is an argument with which, the SUM function knows the column on which it has to aggregate a SUM.
Simply put, you can read it as, “For the measure named Total Profit, calculate (=) the SUM of values in the [Profit] column in the Orders table.”
Functions – DAX functions are predefined formulas that take some parameters. They perform calculations using arguments passed on to them. These arguments can be numbers, text, logical values, or other functions.
Context – DAX uses context to determine which rows should be used to perform a calculation. Context enables you to perform dynamic analysis, in which the results of a formula can change to reflect the current row or cell selection and also any related data. When we speak of context, this may refer to one of the two types; Row context and Filter context.
Row Context: Row context does the calculation for each row and the values within that row. The row context tells DAX which row to use when it needs to obtain the value of a column. We can think of the row context as the “current row” in a table.
Filter Context: The filter context is the set of filters applied to the data model before the evaluation of a DAX expression starts. It is the set of filters that are applied before that table arrives for use. Some initial level filter context is applied on the Power BI report page in the form of Slicer.
Where are DAX Formulas Used in Power BI?
Calculated Columns: When we create a data model on the Power BI Desktop, we can extend a table by creating new columns. The content of the columns is defined by a DAX expression, evaluated row by row or in the context of the current row across that table. In data models for DAX, however, all calculated columns occupy space in memory and are computed during table processing. for example, we have created a new column for Profit in our Orders table using the DAX formula Profit = Orders[Sales] - Orders[Cost] . A new column named Profit is created as shown below.
Measures: Measures enable the user to create fields with aggregate values like sum, count, average, ratio, percentage, etc. The measures are created from the modeling tab of Power BI Desktop. When you define a formula for a measure in the formula bar, a Tooltip feature shows a preview of what the results would be for the total in the current context. For instance, we have created a measure Total Profit using the DAX formula Total Profit = SUM(Orders[Profit]). As we can see a new measure named Total Profit is created under the Orders table.
Calculated Table: A calculated table is a computed object, based on a formula expression, derived from all or part of other tables in the same model. Instead of querying and loading values into your new table's columns from a data source, a DAX formula defines the table's values.
DAX Functions
In Power BI, we can use different function types to analyze data and create new columns and measures. It includes functions from different categories such as −
Aggregate
Text
Date
Logical
Mathematical
Information
1. Aggregate Functions: These functions calculate a (scalar) value such as count, sum, average, minimum, or maximum for all rows in a column or table as defined by the expression.
MIN: This DAX function returns the minimum numeric value in a column, or between two scalar expressions.
Syntax
MIN(<column>)
Example
Min_Profit = MIN(Orders[Profit])
MAX: This DAX function returns the maximum value in a column, including any logical values and numbers represented as text.
Syntax
MAX(<column>)
Example
Max_Profit = MAX(Orders[Profit])
SUM: This DAX function adds all the numbers in a column.
Syntax
SUM(<column>)
Example
Total_Sales = SUM(Orders[Sales])
AVERAGE: This DAX function returns the arithmetic mean of the values in a column.
Syntax
AVERAGE(<column>)
Example
Average_Sales = AVERAGE(Orders[Sales])
2. Text Functions: With these functions, we can return part of a string, search for text within a string, or concatenate string values.
CONCATENATE: This DAX function joins two text strings into one.
Syntax
CONCATENATE(<text1>, <text2>)
Example
=CONCATENATE("First_Name ", "Last_Name")
CONCATENATEX: This DAX function joins the result of an expression evaluated for each row in a table.
Syntax
CONCATENATEX(<table>, <expression>, [delimiter])
Example
=CONCATENATEX(Employees, [FirstName] & “ “ & [LastName], “,”)
FIXED: This DAX function rounds a number to the specified number of decimals and returns the result as text.
Syntax
FIXED(<number>, <decimals>, <no_commas>)
Example
=FIXED([PctCost],3,1)
REPLACE: This DAX function replaces part of a text string, based on the number of characters you specify, with a different text string.
Syntax
REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
Example
=REPLACE('New Products'[Product Code],1,2,"OB")
SEARCH: This DAX function returns the number of characters at which a specific text string is first found.
Syntax
SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])
Example
=SEARCH("n", "printer")
The formula returns 4 because "n" is the fourth character in the word "printer."
UPPER: This DAX function returns a text string in all uppercase letters.
Syntax
UPPER (<text>)
Example
=UPPER(['New Products'[Product Code])
3. DATE TIME FUNCTIONS: These functions in DAX are similar to date and time functions in Microsoft Excel. However, DAX functions are based on the datetime data types used by Microsoft SQL Server.
DATE: This DAX function returns the specified date in Date-Time format.
Syntax
DATE(<year>, <month>, <day>)
Example
=DATE(2019,12,17)
HOUR: This DAX function returns the specified hour as a number from 0 to 23 (12:00 A.M. to 11:00 P.M.).
Syntax
HOUR(<datetime>)
Example
=HOUR('Orders'[TransactionTime])
TODAY: This DAX function returns the current date.
Syntax
TODAY()
NOW: This DAX function returns the current date and time in Date-Time format.
Syntax
NOW()
EOMONTH: This DAX function returns the date in Date-Time format of the last day of the month, before or after a specified number of months.
Syntax
EOMONTH(<start_date>, <months>)
Example
=EOMONTH("March 3, 2008",1.5)
4. Logical Functions: These functions return information about values in an expression. For example, the TRUE function lets us know whether an expression that we are evaluating returns a TRUE value.
AND: This DAX function performs logical AND(conjunction) on two expressions. For AND to return true, both conditions specified have to be fulfilled.
Syntax
AND(<logical argument1>,<logical argument2>)
Example
=IF(AND(10 > 9, -10 < -1), "All true", "One or more false"
Because both conditions, passed as arguments, to the AND function are true, the formula returns "All True".
OR: This DAX function performs logical OR(disjunction) on two expressions. For OR to return true, either of the two conditions specified has to be fulfilled.
Syntax
OR(<logical argument1>,<logical argument2>)
Example
=IF(OR(10 > 9, -10 >-1), "True", "False"
Because one of the conditions, passed as arguments, to the OR function is true, the formula returns "True".
NOT: This DAX function performs logical NOT (negation) on a given expression.
Syntax
NOT(<logical argument>)
Example
=NOT([CalculatedColumn1])
For each row in Calculated Column1, the NOT function returns the logical opposite of the given value.
IF: This DAX function tests a series of inputs for the one that fulfills the condition specified in the argument.
Syntax
IF(logical_test>,<value_if_true>, value_if_false)
Example
=IF([Calls]<200, "low", IF([Calls]<300, "medium", "high"))
IFERROR: This DAX function evaluates an expression and returns a specified value if the expression returns an error.
Syntax
IFERROR(value, value_if_error)
Example
=IFERROR(25/0,9999)
4. Mathematical Functions: Mathematical functions in DAX are similar to Excel's mathematical and trigonometric functions. However, there are some differences in the numeric data types used by DAX functions.
ABS: This DAX function returns the absolute value of the number given.
Syntax
ABS(<number>)
Example
=ABS([DealerPrice]-[ListPrice])
EXP: This DAX function returns the value of e raised to the power of the given number.
Syntax
EXP(<number>)
Example
=EXP([Power])
FACT: This DAX function returns the factorial of a number.
Syntax
FACT(<number>)
Example
=FACT([Values])
LN: This DAX function returns the natural log of the given number.
Syntax
LN(<number>)
Example
=LN([Values])
LOG: This DAX function returns the log with the base of the given number.
Syntax
LOG(<number>,<base>)
Example
All of the following return the same result, 2.
=LOG(100,10)
=LOG(100)
=LOG10(100)
PI: This DAX function returns the value of Pi.
Syntax
PI()
POWER: This DAX function returns the value of the first argument raised to the power of the second argument.
Syntax
POWER(<number>, <power>)
Example
=POWER(5,2)
QUOTIENT: This DAX function performs division and returns the integer part of the quotient.
Syntax
QUOTIENT(<dividend>, <divisor>)
Example
=QUOTIENT(5,2)
SIGN: This DAX function returns the sign of a given number.
Syntax
SIGN(<number>)
Example
=SIGN( ([Sale Price] - [Cost Price]) )
SQRT: This DAX function returns the square root of the given number.
Syntax
SQRT(<number>)
Example
=SQRT(25)
6. INFORMATION FUNCTIONS: These functions look at a table or column provided as an argument to another function and return whether the value matches the expected type. For example, the ISERROR function returns TRUE if the value you reference contains an error.
ISBLANK: This DAX function returns TRUE or FALSE after checking whether a value is blank.
Syntax
ISBLANK(<value>)
Example
=IF( ISBLANK('CalculatedMeasures'[PreviousYearTotalSales]) , BLANK() , ( 'CalculatedMeasures'[Total Sales]-'CalculatedMeasures'[PreviousYearTotalSales] ) /'CalculatedMeasures'[PreviousYearTotalSales])
ISNUMBER: This DAX function returns TRUE or FALSE after checking whether a value is numeric.
Syntax
ISNUMBER(<value>)
Example
=IF(ISNUMBER(0), "Is number", "Is Not number")
ISTEXT: This DAX function returns TRUE or FALSE after checking whether a value is a text.
Syntax
ISTEXT(<value>)
Example
=IF(ISTEXT("text"), "Is Text", "Is Non-Text")
ISNONTEXT: This DAX function returns TRUE or FALSE after checking whether a value is non-text.
Syntax
ISNONTEXT(<value>)
Example
=IF(ISNONTEXT("text"), "Is Non-Text", "Is Text")
ISERROR: This DAX function returns TRUE or FALSE after checking whether a value is an error.
Syntax
ISERROE(<value>)
Example
=IF( ISERROR( SUM('ResellerSales_USD'[SalesAmount_USD]) /SUM('InternetSales_USD'[SalesAmount_USD]) ) , BLANK() , SUM('ResellerSales_USD'[SalesAmount_USD]) /SUM('InternetSales_USD'[SalesAmount_USD]) )
Conclusion
DAX in Power BI allows the designer to create complex measures and improve the visualizations. The Applied DAX with Power BI has a variety of functions that can be used to execute DAX queries on the Power BI Desktop. DAX library enables you to use many advanced Power BI features. For instance, with DAX, you can manipulate your data dynamically by using select, join, filter, and many more commands.