DAX in Power BI is the expression used by report designers to perform data analysis and calculations. Data Analysis Expression is a part of the Power BI toolkit that enables Business Analysts to use their datasets at the fullest potential. These expressions make report creation more efficient and provide a smooth experience to the designer.
Check out this blog on Power BI to learn more about the topic.
Let’s go through the list of the topics we’ll be discussing:
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 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 DAX tutorial for more information:
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 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.
The process of implementing DAX expressions resembles the creation of formulas in Microsft Excel. The knowledge of the Exel spreadsheet would be helpful, but you can also use DAX without any experience in Excel. For understanding the formulas and calculations, you should be familiar with Power BI Desktop and the fundamentals of measures, statistics, and probability.
Check out the Power BI Interview Questions if you’re preparing for a job interview.
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
DAX Calculation Types
DAX in Power BI has to tyes of calculations or formulas used to generate a resultant value from the input values. These are ;
- Calculated Columns: Calculated columns are used to create new columns into existing tables with filtered or sorted malfunction inside them. 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.
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, a column reference, constants, text, 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 has the time intelligence functions used 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.
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.
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.
HOUR(NOW()) //returns 12:00:00AM
- DAX DATEVALUE function: Converts the given date into a text-to-date-time format.
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.
DATEADD(<Dates>, <Number_of_Intervals>, <Intervals>)
- DAX DATESBETWEEN function: The return table containing a column of dates between the start date and end date.
DATESBETWEEN(<Dates>, <StartDate>, <EndState>)
CALCULATE(SUM(Sales([Sales Amount]), DATESBETWEEN(Sales[Date], Date(2020,1,1), Date(2020,3,31)))
- DAX LAST DATE function: LASTDATE returns the date the context of the current date.
- DAX ENDOFYEAR function
DAX logical functions are used to perform logical operations and the value returned by the would-be 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.
- DAX OR function: The function will return True if at least one or both the argument is True, otherwise False.
OR([Medal Count]<100, [Count of Sports]>100)
- DAX IF function:
It checks the first arguments given in the statement. The function returns the first value if the condition is True and returns the second if the condition is False.
IF(<condition>, <first_value>, <second_value>)
- DAX SWITCH function:
The function evaluates arguments and returns one of the values listed against it.
SWITCH(<argument>, <value>, <result>, <value>, <result>, <value>, <result>, <value>, <result>….,[<Else>])
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.
ABS(-6) //returns 6
- DAX CURRENCY function
CURRENCY(6.0) //returns 6
- DAX SQRT function:
SQRT function returns the square root of a given number
SQRT(36) //returns 6
SQRT(625) //returns 25
- DAX LOG10 function:
For a positive number, the function returns the value in base 10 logarithmic.
LOG10(10) //returns 1
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 used to show the variation in the percentage across the sample.
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.
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.
CONFIDENCE.NORM(alpha, deviation, size)
CONFIDENCE.NORM(0.05, 2.5, 50) //returns 0.692951912174839
The function returns the geometric mean of the given column at decimal places.
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.
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.
- DAX FIND function:
The function search for the given string and returns the starting position of the string.
FIND(<find_text>, <within_text>, [<StartingValue>], [DefaultValue])
FIND([ProductName], [Description], , BLANK())
- DAX SUBSTITUTE function:
The function replaces the selected text with a new one in a given string.
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.
- DAX PATHCONTAINS function: Check if the given item resides in the specified path, otherwise False.
PATHCONAINS(“Emp0004 | Emp0005 | Emp0006 | Emp00014”, “Emp0006”) // returns True
- DAX PATHLENGTH function: It returns the number of parents of a given Path item.
Go through this blog on Power BI Desktop and learn how to install create reports on the software.
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:
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 n your directory.
- 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.
- 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.
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 which can be used to execute DAX query in 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.