Power BI DAX String Functions

Let me show you how to use Power BI DAX string functions with examples. Microsoft Power BI DAX provides several string functions such as LEN, LEFT, RIGHT, LOWER, UPPER, MID, SUBSTITUTE, FORMAT, CONCATENATE, CONCATENATEX, REPT, UNICHAR, VALUES, etc.

To demonstrate these Power BI DAX string functions, we are going to use the data shown below. As you can see, there are 15 records in this Power BI table.

The following series of examples shows the list of DAX string functions in Power BI

Power BI DAX LEN function

The DAX Now function returns the length of a given string. The syntax of this Power BI DAX LEN is

LEN (string)

To demonstrate these DAX string functions in Power BI, we have to use the calculated column. To create a column, click the New column option under the Home tab, or Modeling tongue.

As you can see in the screenshot below, we renamed the default column to LEN. The following declaration finds the length of a character string in the Department Name column.

LEN = LEN (EmployeeSales [Department Name])

Let me add this Power BI DAX LEN column to the table we created earlier. See the Create Table Report article to understand the steps required to create a table.

Power BI DAX LEFT function

The Power BI DAX LEFT function returns the leftmost characters from a string to a specified index position. The syntax for the Power BI DAX LEFT function is

LEFT (string, position)

The following statement returns the leftmost 8 characters of the Department Name column

LEFT = LEFT (EmployeeSales [Department Name], 8)

Power BI DAX RIGHT feature

The Power BI DAX RIGHT function returns the rightmost characters from a string to a specified index position. The syntax for the Power BI DAX RIGHT function is

RIGHT (string, position)

Returns the 6 characters furthest to the right of the Department Name column

RIGHT = RIGHT (EmployeeSales [Department Name], 6)

Power BI DAX LOWER function

The Power BI DAX LOWER function converts the given character string to lowercase. The syntax of the Power BI DAX LOWER function is

LOWER (string)

The next lower Power BI DAX function converts the department name column to lowercase

LOWER = LOWER (EmployeeSales [Department Name])

TOP function of Power BI DAX

The Power BI DAX UPPER function converts the given string to uppercase. The syntax for the Power BI DAX UPPER function is

UPPER (string)

The next top function of Power BI DAX converts the department name column string to uppercase

UPPER = UPPER (EmployeeSales [Department Name])

Power BI DAX MID role

The Power BI DAX MID function returns a substring of the original string. The syntax for the Power BI DAX MID function is

MID (string, starting_position, length)

This Power BI DAX MID function accepts three arguments:

Starting position: substring starts from this position
Length: total length of a substring.

Returns a substring from the Department Name column. The substring begins at position 4 and ends when the length of the string reaches 7.

MID = MID (EmployeeSales [Department Name], 4, 7)

Power BI DAX REPT function

La función REPT de Power BI DAX repite una cadena el número de veces especificado por el Username. La sintaxis de la función REPT de Power BI DAX es:

REPT (string, no_of_times)

Repeat the data in the Last Name column 2 times.

REPT = REPT (EmployeeSales [LastName], 2)

Power BI DAX SUBSTITUTE function

The SUBSTITUTE function in Power BI DAX repeats a string the number of times specified by the user. The syntax for this Power BI DAX SUBSTITUTE function is:

SUBSTITUTE (string, old_string, new_string)

Reemplaza la palabra software with Web dentro de los valores de la columna Nombre del departamento

SUBSTITUTE = SUBSTITUTE (EmployeeSales [Department Name], "Software", "Web")

power-bi-dax-string-functions-10-1283889

Power BI DAX UNICHAR function

The Power BI DAX UNICHAR function returns the Unicode character for the given ASCII value. The syntax for the Power BI DAX UNICHAR function is:

UNICHAR (number)

Returns the Unicode characters of the annual income divided by 2

UNICHAR = UNICHAR (EmployeeSales [Yearly Income] / 2)

power-bi-dax-string-functions-11-4451983

Power BI DAX EXACT function

The DAX EXACT function compares two strings and returns true if they are exactly the same; otherwise it returns false. The syntax for this Power BI DAX EXACT function is:

EXACT (string1, string2)

Below Power Bi DAX Exact Wording compare the department name with the leftmost 18 characters of the department name

EXACT = EXACT (EmployeeSales [Department Name], LEFT (EmployeeSales [Department Name], 18))

power-bi-dax-string-functions-12-8519447

CONCATENATE function of Power BI DAX

The CONCATENATE function in Power BI DAX is useful for concatenating two strings. The syntax for this Power BI DAX CONCATENATE function is:

CONCATENATE (string1, string2)

The following Power BI DAX CONCATENATE function concatenates the first and last name

CONCAT = CONCATENATE (EmployeeSales [FirstName], EmployeeSales [LastName])

power-bi-dax-string-functions-13-4753351

Power BI DAX CONCATENATEX function

The DAX CONCATENATEX function is to concatenate all the rows in a column using the specified delimiter. The syntax for this Power BI DAX CONCATENATEX function is:

CONCATENATEX (tableName, ColumnName, Delimiter)

The following Power BI DAX statement concatenates all the rows in the last name column using a comma delimiter.

CONCATENATEX = CONCATENATEX (EmployeeSales, EmployeeSales [LastName], ",")

power-bi-dax-string-functions-14-4787064

Power BI DAX FIXED function

The Power BI DAX FIXED function is useful for rounding the given number to a specific number of digits and returns the data type of text. The syntax for the Power BI DAX FIXED function is:

FIXED (number, decimals, comma)

The following Power BI DAX FIXED function rounds sales decimals to a single digit, and will not allow commas

FIXED = FIXED (EmployeeSales [Sales], 1, 1)

power-bi-dax-string-functions-15-6539274

Power BI DAX BLANK function

The Power BI DAX BLANK function is useful for returning a blank space. You can also use this to check if the crows have blanks or not. The syntax for this BLANK Power BI DAX function is:

BLANK()

The If statement then checks for blanks when dividing Sales by Grade of Service; if true, the blank is replaced by 100. Otherwise, it returns the result.

BLACNK = IF (DIVIDE (EmployeeSales [Sales], EmployeeSales [Service Grade]) = BLANK (), 100, DIVIDE (EmployeeSales [Sales], EmployeeSales [Service Grade]))

power-bi-dax-string-functions-16-7031555

In the screenshot below, you can see the result.

power-bi-dax-string-functions-17-9808468

Power BI DAX UNICODE function

The DAX UNICODE function returns the ASCII value of the first character in a string. The syntax for this Power BI DAX UNICODE function is:

UNICODE (string)

Returns the ASCII value of the first character in the last name column

CODE = UNICODE (EmployeeSales [LastName])

power-bi-dax-string-functions-18-1162702

Power BI DAX COMBINEVALUES function

The DAX COMBINEVALUES function combines two or more strings with the specified delimiter. The syntax for this Power BI DAX COMBINEVALUES function is:

COMBINEVALUES (Delimiter, string1, string2, ..)

The following declaration combines the First Name, Last Name, Education columns using a comma delimiter.

CONCATENATEX = CONCATENATEX (EmployeeSales, EmployeeSales [LastName], ",")

power-bi-dax-string-functions-19-4235280

Power BI DAX FORMAT function

The Power BI DAX FORMAT function formats numbers and dates in predefined formats. The syntax for the Power BI DAX FORMAT function is:

FORMAT (expression, format_type)

The following Power BI DAX FORMAT function formats the sales amount in currency type

FORMAT = FORMAT (EmployeeSales [Sales], "Currency")

power-bi-dax-string-functions-20-5743783

Power BI DAX VALUE function

The DAX VALUE function converts the string numbers to a number. The syntax for the Power BI DAX VALUE function is:

VALUE (string)

The following declaration converts the string numbers in the result of the fixed function to a number

VALUE = VALUE (EmployeeSales [FIXED])

power-bi-dax-string-functions-21-4865399

To demonstrate these Power BI DAX string functions, we are going to use the data shown below. As you can see, there are 15 records in this Power BI table.