Dax in Power BI: Types, Uses and Examples

DAX, also known as Data Analysis Expressions, is a formula language that is used in Power BI, allowing users to create customized measures and calculations for their data. It has similarities with Excel but focuses more on complex and relational data. DAX in Power BI can create calculated tables and columns. DAX functions in Power BI can perform various tasks, including, the calculation of ratios, performing complex calculations, and filtering the data.

DAX supports various functions including logical, textual, and mathematical operations. It is a powerful tool that allows users to perform complex calculations with ease, and create custom calculations and measures for the desired data. One can get a better and more detailed understanding of the subject, through a Power BI course.

In this blog, we will learn more about what is dax formulas in power bi, its importance, and its uses.

What is DAX in Power BI?

The DAX (Data Analysis Expressions) function is a mathematical language used to build custom computations and measurements on data models in Power BI, Excel Power Pivot, and SQL Server Analysis Services (SSAS).

The DAX function’s primary aim is to assist users in analyzing and summarizing data by generating sophisticated formulae and expressions. DAX allows users to execute complicated calculations and modify data by utilizing functions comparable to Excel formulae but with more advanced capabilities including time intelligence functions, table functions, and filtering functions.

Users can utilize DAX in power bi to do extensive data filtering and sorting, as well as produce complicated computations like running totals, moving averages, and year-to-date totals. These computations can be used to generate customized power bi reports, infographics, and dashboards that give useful data insights. DAX functions in Power BI show many similarities with Excel, but they are mostly used to perform relational tasks and calculations. 

Types of Power BI DAX functions

Below are a few types of Power BI DAX functions: 

• Aggregation Functions: This function in Power BI is used to work with the numbers, it includes operations like SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, DISTINCT COUNT, SUMX, AVERAGEX, MINX, and MAXX.
• Time Intelligence Functions: These functions provide insight into the date and dimensions of time, it includes operations like TOTALYTD, TOTALQTD, TOTALMTD, SAMEPERIODLASTYEAR, DATESYTD, and  DATEADD.
• Text Functions: This function works with tables and columns, it constitutes operations like CONCATENATE, UPPER, LOWER, SUBSTITUTE, TRIM, SEARCH, FIND, LEFT, and RIGHT.
• Logical Functions: It is one of the most unique DAX functions in Power BI, it works with returning the logical values and includes TRUE, FALSE, IF, AND, OR, NOT, SWITCH, and IFERROR.
• Information Functions: This function works by letting the user know if the value matches the expected type, it includes the following operations, ISBLANK, ISNUMBER, ISTEXT, and ISLOGICAL.
• Statistical Functions: This function performs the statistical operations and includes STDEV.P, VAR.P, MEDIAN, PERCENTILE, RANKX, and AVERAGEX.
• Parent-Child Functions: This function is used to perform the task of managing the data and presenting it in a hierarchical order, it includes operations like PATH, PATH CONTAINS, and PATH LENGTH.

Aggregation Functions 

This DAX function in Power BI is mainly used to perform calculations on a specific set of values and is expected to return a single value as a result. It can also be called the summarization and aggregation of the data, below are a few operations that are commonly used in the aggregation function:

• SUM: This operation calculates the sum of the numerical values in a column.
• AVERAGE: This operation is used to calculate the average of numerical values in a column.
• MIN: This operation is used to return the smallest value of numerical value from a column.
• MAX: This operation is used to return the largest value of numerical value from a column.
• COUNT: This operation counts the number of non-blank values in a column.
• COUNTA: This operation works by counting the number of non-empty values in a column.
• DISTINCT COUNT: It counts the number of distinct non-blank values in a column.
• SUMX: This operation is used to calculate the sum for each row in a column or a table. 
• AVERAGEX: This operation is used to calculate the average for each row in a column or a table. 
• MINX: This operation is used in a calculation to return the smallest value for each row in a column or a table.
• MAXX: This operation is used in a calculation to return the largest value for each row in a column or a table.

Enroll in the best data science course with placement to start your career as a data scientist.

Time Intelligence Functions

This Dax function in Power BI is used to support the calculations to compare and aggregate data over a period of time, which can include days, months, quarters, and years. Following below are a few operations for which time intelligence functions are used: 

• DATEADD: This operation’s work is to move the given set of dates by a specific interval of time.
• DATESYTD: This operation focuses on returning a set of dates in the year up to the last date that is visible in the filter context.
• SAMEPERIODLASTYEAR: This operation returns a set of dates from the previous year in the current selection.
• TOTALMTD: This operation has the work of evaluating the expression in the specified date column after applying specified filters over the interval which begins on the first day of the month and ends on the last.
• TOTALQTD: This operation has the work of evaluating the expression in the specified date column after applying specified filters over the interval which begins on the first day of the quarter and ends on the last.
• TOTALYTD: This operation has the work of evaluating the expression in the specified date column after applying specified filters over the interval which begins on the first day of the year and ends on the last.

Text Functions

This Dax in Power BI allows the user to manipulate the text data after analyzing it, it makes the extraction of complex text data easier for the users. Following below are a few operations that this function uses: 

• UPPER: This operation converts all the characters to uppercase in a text string.
• LOWER: This operation converts all the characters to lowercase in a text string.
• CONCATENATE: This operation works to combine two or more then two text strings into one text string.
• SUBSTITUTE: This operation replaces a specific text value in a text string with a different text value.
• TRIM: This operation removes the extra spaces from a text string.
• FIND: This operation helps find the position of a text string within another text string.
• LEFT: This operation returns the leftmost characters based on a specific number of characters from a text string.
• RIGHT: This operation returns the rightmost characters based on a specific number of characters from a text string.

Logical Functions

Power BI DAX function allows the users to evaluate the logical expressions and return a specific result, it performs conditional calculations, filters data, and controls calculator flow. Following below are a few operations that are used frequently in this function:

• TRUE: This operation returns TRUE as the logical value.
• FALSE: This operation returns FALSE as the logical value.
• IF: This operation returns the logical value if one condition is true and if the other condition is false.
• AND: This operation returns TRUE if all the values are specified as true, or otherwise it returns FALSE if the values are specified as false.
• OR: This operation returns TRUE as the logical value if at least one point evaluates TRUE, or else FALSE is returned.
• NOT: This operation returns the opposite logic of the value, which is TRUE if the value is FALSE, and FALSE if the value is TRUE.
• SWITCH: This operation evaluates the expression against the list of values with the first matching value and returns the associated result.
• IFERROR: This operation returns the value of a specified formula that evaluates to an error, otherwise the result of the formula is returned.

Information Functions

This DAX function in Power BI allows the user to retrieve information about a cell, a range of cells, and a table. It can manipulate texts and numerical data, following below are a few operations that this function performs:

• ISBLANK: This operation returns the logic TRUE if the value is blank, but it returns FALSE otherwise.
• ISNUMBER: This operation returns the logic TRUE if the value is a number, but it returns FALSE otherwise.
• ISTEXT: This operation returns the logic TRUE if the value is text, but it returns FALSE otherwise.
• ISLOGICAL: This operation returns the logic TRUE if the value is logical, but it returns FALSE otherwise.

Statistical Functions

This DAX function in Power BI allows the user to perform statistical analysis and calculations on a range of cells. It analyzes data, performs complex calculations, and identifies trends. Following below are a few operations used by this function: 

• STDEV.P: This operation returns the population standard deviation of a column of numbers.
• VAR.P: This operation returns the population variance of a column of numbers.
• RANKX: This operation returns the rank of a value within a column of values.
• AVERAGEX: This operation returns the arithmetic value of the numbers of a column.

Parent-child Functions

This DAX function in Power BI allows the user to work with the data models following the parent-child hierarchy. It is used to aggregate and filter data and perform complex calculations. Following below are a few operations that are used by this function:

• PATH: This operation works in the parent-child hierarchy to return the path of a member.
• PATHITEM: This operation Returns a specific element of a path by the PATH function.
• FILTER: This operation filters the data in a range of cells or a column based on specific conditions.
• CALCULATE: This operation works by evaluating an expression with a modified filter context.

Uses of DAX Functions in Power BI

Now that we know about the DAX function in power bi. Let us look through some of the uses that it offers and are based on the functions that it possesses:

• Creating columns: DAX functions can be used to create new columns in a table based on calculations that involve one or more columns in the table.
• Filtering Data: DAX functions can be used to filter data based on specific conditions, such as date ranges, text matches, or numerical values.
• Creating measures: DAX functions can be used to create new measures that aggregate data in a table or across tables, such as sum, average, and count.
• Creating logic: DAX functions can be used to create complex conditional logic that involves multiple conditions and operations.
• Creating hierarchies: DAX functions can be used to create hierarchies in a data model based on parent-child relationships between tables.

Conclusion

DAX is essentially important for Power BI as it offers smooth working, and flexibility to perform data modeling and function efficiently. It can integrate, aggregate, and customize the data as per the requirement of the users, which makes working with complex numerical calculations and text easier.