28Nov, 2022
Beginner’s Guide to DAX Functions in Power BI DAX

Dax Introduction

The formula expression language DAX (Data Analysis Expressions) is applicable to a variety of BI and visualisation tools. Because the entire code is contained within a function, DAX is also known as a function language. There are two data types in DAX programming formulas: numeric and other. While Other includes string and binary object, Numeric includes integers, money, and decimals.

What is DAX?

The acronym DAX stands for Data Analysis Expressions. Microsoft created this language to interact with data in a number of their platforms, including Power BI, PowerPivot, and SSAS tabular models. It is intended to be straightforward and simple to learn while demonstrating the strength and adaptability of tabular models. You might liken it to Excel formulas taken to the next level.

Using DAX will truly unleash the capabilities of Power BI.

I have reached a point where I use a lot of the code in my daily work even though I am still far from a DAX expert and have coworkers who are beginning to ask more and more questions about it. I decided to write this article about why you should (or shouldn’t) use this tool from the data science/data analysis toolbox as a result.

Essential Qualities of DAX Functions

Measures:

To perform dynamic computations, DAX Functions contain formulas referred to as Measures. You can define your Measures using the DAX formula bar, and these Measures have the property of changing their values based on the situation. Additionally, measures give you the freedom to switch between tables without losing any functionality.

Row-level Security:

The row-level security of your data tables is always maintained by DAX Functions. A DAX evaluation always yields a boolean result in order to guarantee this (True or False). This limits the number of rows that can be set at once and prevents other users from accessing the assigned row. Additionally, DAX functions employ filters in order to maintain the relationship that is currently in use.

Calculated Columns:

The DAX library provides calculated columns for storing the results of computations you do on data. These procedures are typically performed during a data refresh and include two or more columns. Additionally, these Calculated Columns are made to carry out row-level calculations and then create a new column that contains the results.

DAX Functions

You can use several function types in Power BI to analyse data and add new columns and metrics. It consists of operations from several categories, including

  • Aggregate
  • Text
  • Date
  • Logical
  • Counting
  • Information

Aggregate Functions

DAX has a number of aggregate functions.

 
  • MIN
  • MAX
  • Average
  • SUM
  • SUMX

Counting Functions

Other counting functions in DAX include −

 
  • DISTINCTCOUNT
  • COUNT
  • COUNTA
  • COUNTROWS
  • COUNTBLANK
  • Logical Functions
 

Following are the collection of Logical functions −

 
  • AND
  • OR
  • NOT
  • IF
  • IFERROR

 

TEXT Functions

  • REPLACE
  • SEARCH
  • UPPER
  • FIXED
  • CONCATENATE
  • DATE Functions
  • DATE
  • HOUR
  • WEEKDAY
  • NOW
  • EOMONTH
  • INFORMATION Functions
  • ISBLANK
  • ISNUMBER
  • ISTEXT
  • ISNONTEXT
  • ISERROR

DAX Functions for Power BI: Date and Time Functions

DATE: The pre-specified date is returned by this function as output in datetime format. It works using the syntax shown below:
DATE(<year>, <month>, <day>)

DATEVALUE: This function transforms a date’s textual representation into a datetime format. It works using the syntax shown below:

DATEVALUE(date text)
TODAY: The following syntax is used when this function is in operation and returns the current date as output:
TODAY()

WEEKDAY: Using a date as its argument, this function produces a number (1–7) that represents the day of the week for that date. It works using the syntax shown below:
WEEKDAY(<date>, <return_type>)

HOUR: This function provides a number that represents the current hour ( 0 for 12:00 A.M. and 23 for 11:00 P.M.). It works using the syntax shown below:

HOUR(<datetime>)

Information Functions for DAX in Power BI

The output of DAX Information functions is either True or False after analysis of the input argument. This function class takes one or more cells or rows from your Power BI table as parameters. According to DAX, the following operations are classified as informational:

CONTAINS:

Depending on the values for the referred columns, this function either returns true or false. It returns true if all of the values are present; otherwise, it returns false. It works using the syntax shown below:

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

CUSTOMDATA: 
This function retrieves the value of the CustomData property from the connection string after reading it. 
It uses the following procedures to function:

 

CUSTOMDATA()

LOOKUPVALUE:

This function locates the row that satisfies the search columnName and search value criteria in their entirety and returns the value from the result columnName. It works using the syntax shown below:

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

USERNAME:

During connection setup, you provided the system with your domain and user name, which are returned by this function. It works using the syntax shown below:
USERNAME()

ISBLANK:

After examining the input value, this function returns true for blank and false for any other value. It works using the syntax shown below:
ISBLANK(<value>)

The other important functions under Information Functions are as follows:

ISERROR
ISEVEN
ISLOGICAL
ISNUMBER
SODDING
ISTEXT

 

DAX Functions for Power BI: 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:

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>)

NOT: The value of the input expression is changed by this function to its polar opposite. This suggests that it can switch between being true and false. It works with the syntax shown below:
NOT(<logical>)

OR: If any of the arguments present in the input expression are valid, this function returns true; otherwise, it returns false. It works with the syntax shown below:
OR(<logical1>,<logical2>)

SWITCH: Using a list of values and an input expression, this function compares the results and returns one of the possible results. It works using the syntax shown below:
SWITCH(“expression”, “value,” “result,” “value,” result,”…”, “else,”)

IF: If the first argument is true, this function checks the condition and, based on that, returns a true or false condition. It works using the syntax shown below:

If (logical test), “value if true,” ,”value if false,”

The following are some additional crucial logical functions:

  • TRUE
  • FALSE
  • IFERROR
  • IN

Trigonometric and mathematical DAX functions for Power BI

You may easily carry out fundamental mathematical operations using the DAX mathematical and trigonometric functions, which operate similarly to their Excel equivalents. The DAX category “Mathematical & Trigonometric” includes the following functions:

ABS :The following syntax is used by the ABS function to return the input number’s absolute value:

ABS(<number>)

ACOS :The inverse cosine value of a given input integer is returned by the ACOS function. The angle it returns is measured in radians, from 0 to pi. It works using the syntax shown below:

ACOS(number)

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

CEILING(“number,” “importance”)

COMBIN:This function gives the total number of combinations that can be made from the input items. It works using the syntax shown below:

COMBIN(number, number_chosen)

CURRENCY: This function analyses the input and outputs the result as a data type for currency. It works using the syntax shown below:

CURRENCY(<value>)

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

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

Power BI DAX Functions: Parent & Child Functions

You can manage data that is restricted in a parent-child hierarchy using the DAX Parent and Child functions. The Parent & Child category in DAX includes the following functions:

PATH: This function gives back a delimited text string containing all of the current identifiers’ parent identifiers. It works using the syntax shown below:
PATH(‘ID’ column, ‘parent’ column)

PATHITEM: This function extracts the item from a string at the specified position after reading the PATH function. It works using the syntax shown below:
PATHITEM(“path,” “position,” “type,”
The following are some additional crucial responsibilities under Parent & Child:

  • PATHITEMREVERSE
  • PATHLENGTH
  • PATHCONTAINS

DAX Functions for Power BI: Statistical Functions

Averages, approximations, and many other statistical formula operations are supported by DAX statistical functions. The statistical functions listed below are classified as such in DAX:

The ADDCOLUMNS function adds calculated columns to the table that is supplied. The syntax it uses is ADDCOLUMNS(table>, name>, expression>[, name>, expression>]…)

AVERAGE: Using the following syntax, this function determines and returns the arithmetic mean of all the values in a column of input data:
AVERAGE(<column>)

BETA.INV: This function returns the beta cumulative probability density function’s inverse value. It works using the syntax shown below:
BETA.INV(probability,alpha,beta,[A],[B])

The other important functions under Statistical are as follows:

  • CONFIDENCE.
  • ORM
  • COUNT
  • COUNTBLANK
  • COUNTROWS
  • CROSSJOIN
  • DISTINCTCOUNT
  • EXPON.DIST
    GENERATE
  • GENERATEALL
  • GEOMEA
  • GEOMEANX
  • MAX

DAX Functions for Power BI: Text Functions

You can use the String data type while working with tables and columns using DAX Text methods. With the help of these functions, you may alter different aspects of a string, search for specific text inside strings, manage date formats, and much more. The Text category in DAX includes the following functions:

The syntax for this function, which just returns a blank, is as follows:
BLANK()
The first character in the input text string corresponds to the numeric value (code) that this function returns. The syntax for IT is as follows:

CODE(text)

CONCATENATE: Using the following syntax, this function joins two input text strings into one text string.
CONCATENATE(“text1” “text2”);

EXACT: This function compares two text strings and returns a true value if they are precisely the same. It works using the syntax shown below:

EXACT(<text1>,<text2>)

Find:A text string’s beginning within another input text string is returned by the FIND function. It works using the syntax shown below:
FIND(“find text,” “inside,” “start num,”
[, NotFindValue]])

The other important functions under Text are as follows:

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

DAX Functions for Power BI: Other Functions

DAX Functions for Power BI: 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
  • ISSELECTEDME
  • SURE
  • NATURALINNE
  • JOIN
  • NATURALLEFT
  • UTERJOIN
  • SELECTEDMEASURE
  • FORMATSTRING
  • SUMMARIZECOLUMNS
  • UNION
  • WAS

Benefits of DAX Functions

By itself, Power BI offers you amazing functionality. However, adding DAX Functions to Power BI will further your efforts and give you the advantages listed below:

Many cutting-edge Power BI features can be used thanks to the DAX library. For instance, you can use DAX’s select, join, filter, and many other commands to dynamically change your data. The Power BI dashboard will then automatically generate data, including Calculated Columns, Tables, and Measures, based on the input that users provide.
You may add intelligence to your Power BI Dashboards by using DAX. Utilizing Power BI’s Calculated Columns and Measures to limit the data that your Dashboard will retrieve and visualise makes this possible. Your latency and complete data can both be increased by a well-written DAX expression.

20 DAX function in Power Bi to get insight