DAX in Power BI: Formulas and Functions

DAX (Data Analysis Expressions) in Power BI is the expression used by report designers to perform data analysis and calculations. DAX is a part of the Power BI toolkit that enables business analysts to use their datasets to their fullest potential. These expressions make report creation more efficient and provide a smooth experience for the designer.

Check out this video on Power BI DAX for a better understanding of the topic:

DAX in Power BI

Data Analysis Expressions(DAX) are the collection of formulas, functions, operators, and constants that allows a user to create measures, dimensions, and custom tables. They return one or more values and are used to solve data analysis problems, creating a new relationship between different data variables. 

DAX language is very useful as it allows the 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. It’s important to formulate as they are evaluated from the innermost to the outermost function of the expressions.

There are two primary data types in Power BI DAX functions:

• Numeric: numeric data types include decimals, currency values, integers, etc.
• Non-numeric: it consists of strings, binary objects, etc. 

Check out this Power BI Tutorial to learn more about the topic.

DAX Formula – Syntax

Breaking down a statement into individual elements helps you understand any language with ease. It’s important that your study the syntax of these expressions and be able to create the new ones as per the requirements. Consider the image shown below as an example of the DAX depression:

It represents a custom formula used to create new columns by multiplying the values of the other two columns. Let’s break it down and understand what each element does:

• Test Column: Name of the new measure
• (=) Sign: Indicates the starting of your DAX formula
• [Units Sold] and [Manufacturing Price]= These two are the arguments or columns whose values are used to generate the output.
• (*): The ‘*’ operator multiplies the values of the two-column variables.
• [Segment]= Segment represents the classification of the corresponding formula. Unlike regular columns, the calculated columns are necessary to have at least one.

Importance of DAX in Power BI

It’s important to learn the DAX functions in Power BI as they help you implement the functionalities like data transformation and visualization. With basic knowledge of the Power BI interface, you can create decent reports and share them online. However, for calculation and dimensional analysis, you need to know how Power BI DAX functions are carried out.

For example, you can calculate the growth percentage and visualize the growth percentage in different regions of a country to compare the data over the years. DAX in Power BI helps a designer create new measures, which in turn helps a business to identify the problems and find appropriate solutions. 

Check out the Power BI Interview Questions For 3 Years Experience if you’re preparing for a job interview. 

Check out our blog on Top Power BI Projects Ideas!

DAX Functions

DAX Functions in Power BI are the predefined formulas used to calculate the arguments in a function, executed in a particular order. These arguments could be numbers, constants, texts, another function or formula, and logical values such as True or False. The functions perform particular operations on one or more arguments in a DAX formula. Below are the key points of DAX functions:

• DAX functions in Power BI will never refer to individual values, they always refer to a complete field, column, or table. However, you have to create filters inside the DAX formula, if you want to use DAX functions on individual values. 
• DAX functions can also be applied for separate rows without any filters. The calculations can be applied based on the context of each row. 
• DAX uses the time intelligence function to calculate the time and date ranges. We’ll discuss these functions below in detail.
• These functions can sometimes return the entire table, which can be used as an input for other DAX functions in Power BI. However, the user cannot display these output tables returned by the functions. 

Want to become a master in Power BI check out this Power BI Course in New York?

Types of DAX Functions

Following are the Power BI DAX functions with examples to carry out the calculations: 

Date and Time Functions

Similar to Excel, these functions are used to calculate the date and periods in the DateTime format. Below are some of the Date and time functions with their syntax:

DAX CALENDAR function:  

The start-date and end-date arguments could be any DateTime value. It returns the table of a single column with a set of dates.
Syntax:
CALENDAR(<StartDate>,<EndDate>)
Example:
COUTDAYS(CALENDAR(DATE(2020,4,1), DATE(2020,6,5))) //returns 65

DAX DATEDIFF function:

The DATEDiff function calculates the difference between two dates and returns it in terms of interval boundaries given specified by the user.
Syntax:
DATEDIFF(<StartDate>,<EndDate>,<Interval>)
Example:
DATEDIFF(DATE(2020,1,1), DATE(2020,1,31), HOUR)  //returns 720
DATEDIFF(DATE(2020,1,1), DATE(2020,3,31), DAYS)  //returns 90
DATEDIFF(DATE(2020,1,1), DATE(2020,4,31), MONTH) //returns 3

DAX NOW function:

The function returns the current DateTime value in the standard format.
Syntax:
NOW()
Example:
HOUR(NOW()) //returns 12:00:00AM

DAX DATEVALUE function:

Converts the given date into a text-to-date-time format.
Syntax:
DATEVALUE(<DateText>)
Example:
DATEVALUE(“1/4/2020”) //returns
DATEVALUE(“20-3-2020”) //returns 1/20/3/2020 12:00:00AM
DATEVALUE(“15-Jan-2020”) //returns15/1/2020 12:00:00 AM

Time Intelligence Functions

The time intelligence functions allow the user to calculate the time values over a fixed period o time such as weeks, months, quarters, and years. These functions are mainly used to calculate aggregation, per data manipulation, and Business Intelligence with the table of dates as input. 

DAX DATEADD function

Returns a table with the columns of dates shifted, either forward or backward, based on the specified intervals of time.
Syntax:
DATEADD(<Dates>, <Number_of_Intervals>, <Intervals>)
Example:
DATEADD(ProductInventory[InventoryDate],1,YEAR)

DAX DATESBETWEEN function

The return table contains a column of dates between the start date and the end date.
Syntax:
DATESBETWEEN(<Dates>, <StartDate>, <EndState>)
Example:
CALCULATE(SUM(Sales([Sales Amount]), DATESBETWEEN(Sales[Date], Date(2020,1,1), Date(2020,3,31)))

DAX LAST DATE function

returns the last date of the modifications done in the date columns. For example, the command mentioned below will return the last date when a sale was made.
Syntax:
LASTDATE(<Dates>)
Example:
LASTDATE(Sales[Date])

DAX ENDOFYEAR function

Syntax:
ENDOFYEAR(<DATES>, [<Year_End_Date>])
Example:
ENDOFYEAR(Start[Date])

Do you want to learn about the key components of Power BI

Logical Functions

DAX logical functions are used to perform logical operations and return them as either True or False. Below are the DAX logical functions:

DAX AND function

The AND function checks if both arguments are True or False. It will return True if only if both arguments are true, otherwise it will return False.
Syntax:
AND(<argument1>, argument2)
Example:
AND([Country]=”USA”,[Medal]=”Gold”)

DAX OR function

The function will return True if at least one or both the arguments is True, otherwise False.
Syntax:
OR(<argument1>,<argument2>)
Example:
OR([Medal Count]<100, [Count of Sports]>100)

DAX IF function:

It checks the first argument given in the statement. The function returns the first value if the condition is True and returns the second if the condition is False.
Syntax:
IF(<condition>, <first_value>, <second_value>)
Example:
IF([Country]=”India”,1,0)

DAX SWITCH function:

The function evaluates arguments and returns one of the values listed against it.
Syntax:
SWITCH(<argument>, <value>, <result>, <value>, <result>, <value>, <result>, <value>, <result>….,[<Else>])
Example:
SWITCH([Weekdays], 0, ”Monday”, 1, ”Tuesday”, 2, ”Wednesday”, 3, ”Thursday”, 4, ”Friday”, 5, ”Saturday”, 6, ”Sunday”, ”Unknown”)

Mathematical and Trigonometric Functions

These functions are very similar to the mathematical and trigonometric functions of Microsoft Excel. They are used to perform all sorts of calculations in Power BI. Some of the popular mathematical and trigonometry functions are:

DAX ABS function

The function returns the absolute value of a given number, meaning it will remove the sign from the number. 
Syntax:
ABS(<number>)
Example:
ABS(-6) //returns 6

DAX CURRENCY function

Syntax:
CURRENCY(<value>)
Example:
CURRENCY(6.0) //returns 6

DAX SQRT function:

SQRT function returns the square root of a given number 
Syntax:
SQRT(<number>)
Example:
SQRT(36) //returns 6
SQRT(625) //returns 25

DAX LOG10 function:

For a positive number, the function returns the value in base 10 logarithmic. 
Syntax: 
LOG10(<Value>)
Example:
LOG10(10) //returns 1

Statistical Functions

The statistical functions carry out the DAX expressions used in statistical models and aggregations. Some of these functions with their syntax and example are listed below:

DAX BETA.DIST function

The function performs beta distribution and is used to show the variation in the percentage across the sample. 
Syntax:
BETA.DIST(x, Alpha, Beta, Cumulative, [P,[Q]])
Here, x is the value between the lower bound P and upper bound Q, alpha and beta are the parameters for the distribution, and cumulative determines the form of the function. 
Example:
BETA.DIST(0,5,9,10), TRUE(), 0,1) //returns 0.592735290527344

DAX CONFIDENCE.NORM function:

The function returns the confidence interval of the given sample.
Syntax:
CONFIDENCE.NORM(alpha, deviation, size)
Example:
CONFIDENCE.NORM(0.05, 2.5, 50) //returns 0.692951912174839

GEOMEAN:

The function returns the geometric mean of the given column at decimal places.
Syntax:
GEOMEAN(<column_name>)
Example:
GEOMEAN(Sales[Sales_Amount])

Text Functions

Text functions allow the user to work with the strings in tables and columns. You can get a substring, perform different operations like string concatenation. Some of these functions are listed below:

DAX CONCATENATE function

Combine two strings into one string. 
Syntax:
CONCATENATE(<string1>,<string2>)
Example:
CONCATENATE(“Dwayne”,” Johnson”)  //return Dwayne Johnson

DAX EXACT function:

EXACT is a case-sensitive function that compares two given strings. The function returns True if they are exactly the same, otherwise False.
Syntax:
EXACT(<string1>,<string2>)
Example:
EXACT(Results[Sport],[Sport])

DAX FIND function:

The function search for the given string and returns the starting position of the string.
Syntax:
FIND(<find_text>, <within_text>, [<StartingValue>], [DefaultValue])
Example:
FIND([ProductName], [Description], , BLANK())

DAX SUBSTITUTE function:

The function replaces the selected text with a new one in a given string. 
Syntax:
SUBSTITUTE(<string>,<old_text>,<new_text>)
Example:
SUBSTITUTE([Product], “Hairgel”,”Hairmask”)

Parent-Child Functions

The Parent-Child functions are used to manage the data and represent it in a  hierarchical order. Some of these functions are:

DAX PATH function

Starting from the oldest, the function returns all the identifiers in the form of a delimited string. 
Syntax:
PATH(<Child_ColumnName>,<Parent_CulumnName>)
Example:
PATH(Employee[ID], Employee[ManagerID])

DAX PATHCONTAINS function

Returns True if the given item resides in the specified path, otherwise return False.
Syntax:
PATHCONTAINS(<Path>,<Item>)
Example:
PATHCONAINS(“Emp0004 | Emp0005 | Emp0006 | Emp00014”, “Emp0006”) // returns True

DAX PATHLENGTH function

It returns the number of parents of a given Path item. 
Syntax:
PATHLENGTH(<path>)
Example:
ATHLENGTH(PATH(Employee[ID], Employee[ManagerID]))

Interested in Power BI? Here is the Power BI Training in Bangalore provided by Intellipaat.

DAX Calculation Types

DAX in Power BI has two types of calculations or formulas used to generate a resultant value from the input values. These are:

Calculated Columns: 

Calculated columns are used to merge new columns into existing ones with filters. These columns can be created from the Modeling tab in Power BI Desktop, where new columns can be created by entering their names and formula.

Calculated Measures:

Measures enable the user to create fields with aggregate values like average, ratio, percentage, etc. Just like calculated columns, the measures are created from the modeling tab of Power BI Desktop. 

Now that you’ve learned about the Syntax of DAX formulas, let’s discuss the DAX functions and their types. 

Creating Measures in Power BI

You can create two types of Measures or DAX formulas in Power BI Desktops: Automatic and user-specified. Let’s see how to create each of these DAX expressions in Power BI:

Automatic Measures

Follow the steps mentioned below to let Power BI Desktop create the measures automatically:

• On Power BI Desktop, go to File>> Open Report and locate the report in your system storage.
• Load the datasheet and move the pointer towards the Fields panel on the right side. 

• Now, drag-n-drop any field like ‘SalesAmount’ to the reports panel. You can also click on the check box of the SalesAmount field. 

• New visualization in the form of a column chart will appear showing the total sum of all the values of the SalesAmount column. 
• Instead of showing every value in the column of more than two million rows, Power BI automatically creates an aggregate for these columns. Every field with a Sigma icon has numerical values.
• Depending on the measure you can create or change the chart aggregation from the Value area of the visualizations panel. 

Enroll in our Power BI Certification training and start learning Power BI!

• Create your Measure
  Let’s assume you need a measure that analyzes net sales from total sales amounts. For that, create a measure that calculates the sum of the ReturnAmount and DiscountAmount and subtracts it from the aggregate sum of SalesAmount. So, follow the steps given below:
  • Right-click on the Sales table and click on the ‘NewMeasure’ option. 

• This measure will be saved in the Visualization panel, where you can add more Fields and customize them. 

• Now, select the Sales table, right-click on the Measure and a dialog will appear. You can edit the measure name to make it more identifiable.
• Following that, enter the formula and the fields you want to include in the measure at the top of the interface. 

• The expressions will appear inside the parenthesis where you can enter the column names.
• The syntax of your formula should look something like this:

• Click on the Enter button, then Commit to validate and complete the formula. 
• At last, you can apply the measure and calculate the Net Sales by adding the values to the visualization panel. 

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 in the Power BI Desktop. You can find more information about the DAX functions in the official documentation of Power BI.

Visit our Power Business Intelligence Community for more information on Power BI.