DAX Functions in Power BI: 8 Popular Function Types

DAX (Data Analysis Expressions) is a rich function library that works to simplify the tasks of a Power BI user. Moreover, DAX enables users to implement dynamic Data Management and carry out complex mathematical & logical tasks with just a few clicks. This way, the DAX library optimizes users’ experience of working with Power BI.

This article introduces Power BI and DAX along with their key features. It also discusses the various types of DAX Functions for Power BI. Furthermore, it will provide you with the benefits of using DAX.

What is Power BI?

Power BI is a Microsoft tool that provides you with advanced Business Intelligence and Data Visualization services. You can utilize Power BI to seamlessly analyze raw data, generate actionable insights, and visualize them via comprehensive reports & charts. Moreover, with the Power BI platform, your teams can collaborate on Business Analytics tasks and easily scale them as your business grows. Using this tool, you can implement a data-driven model for your business.

Power BI contains a rich feature set and provides numerous Cloud-based services to enhance your Data visualization. Even users from a non-technical background can rely on its elegant user interface to generate their Data Reports. Power BI also allows you to extract and aggregate data stored in multiple sources and track key business metrics using its Dashboards.

Key Features of Power BI

The following features of Power BI make it so popular in today’s market:

• Data Selection: Power BI provides you with options to filter the datasets and create relevant small chunks of data. This will allow you to focus on individual datasets and perform more insightful Data Analysis. 
• Responsiveness: Power BI relies on highly responsive Navigation Trees and Dashboards on all platforms like Android, iOS, etc. This allows you to get a detailed insight into any Report or Graph without any glitches.
• Seamless Functionalities: Power BI offers you simple drag-and-drop functionalities to create customized reports. This way, even if you have zero to no technical experience, you can easily analyze & visualize your data. Furthermore, Power BI’s platform works on Natural Language Queries. This safeguards you from putting time and resources into learning a complex query language.
• Data Connectivity: Power BI contains a multitude of connectors that allows it to integrate with various external data sources. This way you can directly connect your preferred Data Sources to Power BI seamlessly and perform high-level Data Analytics and visualization. 

What are DAX Functions?

Data Analysis Expressions (DAX) is a large and comprehensive collection of functions and operators that are critical for utilizing many Power BI features. Using the DAX Functions, you can easily build expressions that can automate your Data Analytics work. For instance, you can set up DAX’s functions to build an expression that can calculate and return particular values based on the given expression. In other words, DAX helps you in generating new data using the information that your Power BI model already possesses.

DAX operates on metadata known as Function Reference. It carries detailed information on the syntax, parameters, and returns values for the 250+ functions stored in the DAX library.

Key Features of DAX Functions

DAX library has the following key features:

• Measures: It contain formulas called Measures to carry out dynamic calculations. These Measures have the property of modifying their results according to the context and you can define your Measures via the DAX formula bar. Measures also provide you the flexibility to shift between tables without suffering from any functionality loss.
• Calculated Columns: DAX library offers Calculated Columns to store computational results of your data operations. These operations usually involve 2 or more columns and are done during a data refresh. Furthermore, these Calculated Columns are designed to perform row-level computations and based on them, generate a new column containing the output.
• Row-level Security: DAX Functions always maintain the row-level security of your data tables. To ensure this, a DAX evaluation always provides a boolean result only (True or False). This way only one-row set is allowed at once and no other users are allowed to access the row that was assigned. On top of this, it also use filters to preserve the currently active relationship.

Top DAX Functions for Power BI

Since now you know what are DAX Functions, this section will elaborate on the types and Syntax of the important DAX Functions in Power BI. Using the following 8 Functions can make your Power BI experience hassle-free:

Date and Time Functions

DAX Date and Time functions follow the similar syntax and rules as the Excel date and time functions but operate on DAX’s data type to calculate the values of time and date. The following functions fall under the Date and Time classification in DAX:

1. DATE: This function returns a pre-specified date as output in datetime format. It operates using the following syntax:

DATE(<year>, <month>, <day>)

2. DATEVALUE: This function converts the format of the date from text to datetime. It operates using the following syntax:

DATEVALUE(date_text)

3. TODAY: This function returns the current date as output and operates using the following syntax:

TODAY()

4. WEEKDAY: This function takes in date as input and returns a number( 1 to 7) corresponding to the day of the week of a date. It operates using the following syntax:

WEEKDAY(<date>, <return_type>)

5. HOUR: This function returns the current hour in the form of a number ( 0 for 12:00 A.M. and 23 for 11:00 P.M.). It operates using the following syntax:

HOUR(<datetime>)

The other important functions under Day and Time Functions are as follows:

• MINUTE
• MONTH
• SECOND
• CALENDAR
• CALENDARAUTO
• TIME
• TIMEVALUE

You can learn more about Power BI DAX Count Function, here.

Information Functions

DAX Information functions analyze the input argument and produce a True or False output. The arguments of this function class are a cell or row from your Power BI table. The following functions fall under the Information classification in DAX:

1. CONTAINS: This function returns true or false depending on the values for referred columns. If all the values exist, then it returns true, otherwise false. It operates using the following syntax:

CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…)  

2. CUSTOMDATA: This function reads the connection string and returns its CustomData property content. It operates using the following functions:

CUSTOMDATA() 

3. LOOKUPVALUE: This function finds the row that meets the whole criteria provided by  search_columnName and search_value and returns the value from result_columnName. It operates using the following syntax:

LOOKUPVALUE( <result_columnName>, <search_columnName>,  <search_value>[, <search_columnName>, <search_value>]…) 

4. USERNAME: This function returns your domain and user name that you have submitted to the system during connection setup. It operates using the following syntax:

USERNAME() 

5. ISBLANK: This function returns true for blank and false for any other value after checking the input value. It operates using the following syntax:

ISBLANK(<value>)

The other important functions under Information Functions are as follows:

• ISERROR
• ISEVEN
• ISLOGICAL
• ISNUMBER
• ISODD
• ISTEXT

Logical Functions

DAX Logical Functions evaluate a certain input expression containing logical operators and provide a True or False output. The Logical functions work on evaluating a logical expression and are different from the Information functions which test the input argument by matching it to the stored data. The following functions fall under the Logical classification in DAX:

1. AND: This function returns true if both arguments present in the input expression are valid, else it returns false. It operates using the following syntax:

AND(<logical1>,<logical2>) 

2. NOT: This function changes the value of the input expression to its opposite counterpart. This implies it changes from true to false and vice-versa. It operates using the following syntax:

  NOT(<logical>)  

3. OR: This function returns true if any of the arguments present in the input expression are valid, else it returns false. It operates using the following syntax:

       OR(<logical1>,<logical2>) 

4. SWITCH: This function matches the input expression with a list of values and returns one of the various possible outcomes. It operates using the following syntax:

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])  

5. IF: This function checks the given condition and based on that returns a true or false condition provided the first argument is met. It operates using the following syntax:

IF(logical_test>,<value_if_true>, value_if_false)

The other important functions under Logical Functions are as follows:

• TRUE
• FALSE
• IFERROR
• IN

Mathematical & Trigonometric Functions

DAX Mathematical and Trigonometric functions allow you to perform basic mathematical operations with ease, These functions work similarly to their Excel counterparts. The following functions fall under the Mathematical & Trigonometric classification in DAX:

1. ABS: This function returns the absolute value of the given input number using the following syntax:

ABS(<number>)  

2. ACOS: This function returns the inverse cosine value of a given input number. The angle that it returns is in radians ranging from 0 (zero) to pi. It operates using the following syntax:

ACOS(number) 

 3) CEILING: This function returns the nearest roundup value (integer) of the given input using the following syntax: 

CEILING(<number>, <significance>)  

4) COMBIN: This function returns the number of possible combinations for the number of items given in the input. It operates using the following syntax:

COMBIN(number, number_chosen)

5) CURRENCY: This function evaluates the argument and returns the result as currency data type. It operates using the following syntax:

CURRENCY(<value>)  

 The other important functions under Mathematical & Trigonometric are as follows:

• ASIN  
• ATAN
• COS
• DEGREES
• DIVIDE
• EVEN
• EXP
• FACT

Parent & Child Functions

DAX Parent and Child functions enable you to manage data that is constrained in a parent-child hierarchy. The following functions fall under the Parent & Child classification in DAX:

1. PATH: This function returns a delimited text string with the parent identifiers of all the current identifiers. It operates using the following syntax:

PATH(<ID_columnName>, <parent_columnName>)  

2. PATHITEM: This function reads the PATH function and returns the item at the specified position from a string. It operates using the following syntax:

PATHITEM(<path>, <position>[, <type>])  

The other important functions under Parent & Child are as follows:

• PATHITEMREVERSE
• PATHLENGTH
• PATHCONTAINS

Statistical Functions

DAX Statistical functions support operations related to statistical formulas including averages, approximations, and much more. The following functions fall under the Statistical classification in DAX:

1. ADDCOLUMNS: This function performs the addition of calculated columns of the given table. It operates using the following syntax:

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)  

2. AVERAGE: This function calculates and returns the arithmetic mean of all the numbers of an input column using the following syntax:

    AVERAGE(<column>)  

3. AVERAGEA: This function also returns the arithmetic mean of column values but also manages the text and non-numeric values. It operates on the following syntax:

AVERAGEA(<column>)

4. BETA.INV: This function returns the inverse value of the beta cumulative probability density function. It operates using the following syntax:

BETA.INV(probability,alpha,beta,[A],[B]) 

5. CHISQ.INV: This function returns the inverse value of the left-tailed probability from the chi-squared distribution. It operates using the following syntax:

CHISQ.INV(probability,deg_freedom) 

The other important functions under Statistical are as follows:

• CONFIDENCE.NORM
• COUNT
• COUNTBLANK
• COUNTROWS
• CROSSJOIN
• DISTINCTCOUNT
• EXPON.DIST
• GENERATE
• GENERATEALL
• GEOMEAN
• GEOMEANX
• MAX
• MEDIAN
• MEDIANX
• MIN
• MINX

Text Functions

DAX Text functions allow you to work with tables and columns while using the String data type. Using these functions, you manipulate parts of a string, find certain text in strings, control the formats for dates, and do much more. The following functions fall under the Text classification in DAX:

1. BLANK: This function simply returns a blank and works using the following syntax:

  BLANK()  

2. CODE: This function returns a numeric value(code) corresponding to the first character in the input text string. IT operates using the following syntax:

CODE(text)

3. CONCATENATE: This function joins two input text strings and returns a single text string using the following syntax:

CONCATENATE(<text1>, <text2>)

4. EXACT: This function compares two text strings and if they are exactly the same, it returns a true value. It operates using the following syntax:

EXACT(<text1>,<text2>)

5. FIND: This function returns the starting point of a text string within another input text string. It operates using the following syntax:

FIND(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])

The other important functions under Text are as follows:

• COMBINEVALUES
• FORMAT
• LEFT
• MID
• REPLACE
• RIGHT
• SEARCH
• SUBSTITUTE
• TRIM
• UNICHAR

Other Functions

This category contains DAX Functions that can not be grouped in a specific class due to their unique characteristic properties. The following functions fall under the Other classification in DAX:

• DATATABLE
• ERROR
• EXCEPT
• GENERATESERIES
• GROUPBY
• INTERSECT
• ISEMPTY
• ISSELECTEDMEASURE
• NATURALINNERJOIN
• NATURALLEFTOUTERJOIN
• SELECTEDMEASUREFORMATSTRING
• SUMMARIZECOLUMNS
• UNION
• VAR

Benefits of DAX Functions

Power BI itself provides you with incredible features. However, implementing DAX Functions in Power BI will take you one step further and provide you with the following benefits: 

• 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. This way users can give input to your Power BI dashboard and it will dynamically generate data including Calculated Columns, Tables, and Measures.
• Using DAX, you can make your Power BI Dashboards smarter. This is possible by using Calculated Columns and Measures in Power BI to constrain the data that your Dashboard will fetch and visualize. A well-written DAX expression can decrease your latency and complete data processing using limited resources. 
• DAX operates on a system of nested filter contexts that work to optimize performance. Therefore, working with DAX Functions will shape your thinking pattern when it comes to tables and filtering data. This implies, that your experience with DAX will enhance your thinking and you will find better solutions to merge, filter, and manipulate your data in Power BI and other tools.

Conclusion

This article introduced you to Power BI and DAX and discussed their key features. It also explained the various types of Functions available for the Power BI platform. Furthermore, the article provided the benefits that you can have by using DAX Library when working with Power BI. After reading this article, you can go and experiment with the Power BI DAX Functions and optimize your Data Analytics and Visualization.

Share your views on connecting in the comments section!