Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

Power BI DAX String Functions

by suresh

Let me show you how to use Power BI DAX String Functions with examples. Microsoft Power BI DAX provides various 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 below shown data. As you can see, there are 15 records on this Power BI table.

Power BI DAX String Functions 1

Power BI DAX String Functions

The following series of examples show you 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, please click on the New Column option under the Home tab, or Modeling tab.

Power BI DAX String Functions 2

As you can see from the screenshot below, we renamed the default column name as LEN. The below statement 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 that we created earlier. Please refer to Create Table Report article to understand the steps involved in creating a table

Power BI DAX LEN Function 3

Power BI DAX LEFT Function

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

LEFT(string, position)

The below statement returns leftmost 8 characters from the Department name column

LEFT = LEFT(EmployeeSales[Department Name], 8)
Power BI DAX LEFT Function 4

Power BI DAX RIGHT Function

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

RIGHT(string, position)

It returns rightmost 6 characters from the Department name column

RIGHT = RIGHT(EmployeeSales[Department Name], 6)
Power BI DAX RIGHT Function 5

Power BI DAX LOWER Function

The Power BI DAX LOWER function converts the given string of characters to lower case. The syntax of Power BI DAX LOWER Function is

LOWER(string)

The following Power BI DAX lower function converts the Department name column into lowercase

LOWER = LOWER(EmployeeSales[Department Name])
Power BI DAX LOWER Function 6

Power BI DAX UPPER Function

The Power BI DAX UPPER function converts the given string into uppercase. The Power BI DAX UPPER Function syntax is

UPPER(string)

The below Power BI DAX upper function convert Department name column string into uppercase

UPPER = UPPER(EmployeeSales[Department Name])
Power BI DAX UPPER Function 7

Power BI DAX MID Function

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

MID(string, starting_position, length)

This Power BI DAX MID function accepts three arguments:

  • Starting position – Substring start from this position
  • Length – Total length of a substring.

It returns a substring from the Department Name column. Substring starts at position no 4 and ends when string length reaches to 7.

MID = MID(EmployeeSales[Department Name], 4, 7)
Power BI DAX MID Function 8

Power BI DAX REPT Function

The Power BI DAX REPT function repeats a string for the user-specified number of times. The syntax of the Power BI DAX REPT Function is:

REPT(string, no_of_times)

It repeats the data in the LastName column for 2 times.

REPT = REPT(EmployeeSales[LastName], 2)
Power BI DAX REPT Function 9

Power BI DAX SUBSTITUTE Function

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

SUBSTITUTE(string, old_string, new_string)

It replaces the Software word with Web inside the Department Name column values

SUBSTITUTE = SUBSTITUTE(EmployeeSales[Department Name], "Software", "Web")
Power BI DAX SUBSTITUTE Function 10

Power BI DAX UNICHAR Function

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

UNICHAR(number)

It returns the Unicode characters of Yearly income divide by 2

UNICHAR = UNICHAR(EmployeeSales[Yearly Income] / 2)
Power BI DAX UNICHAR Function 11

Power BI DAX EXACT Function

The DAX EXACT function compares two strings and returns true if they are exactly equal; otherwise, it returns false. The syntax of this Power BI DAX EXACT Function is:

EXACT(string1, string2)

Below Power Bi DAX Exact statement compare Department Name with Leftmost 18 characters of Department Name

EXACT = EXACT(EmployeeSales[Department Name], LEFT(EmployeeSales[Department Name], 18))
Power BI DAX EXACT function 12

Power BI DAX CONCATENATE Function

The Power BI DAX CONCATENATE function is useful to concatenate two strings. The syntax of this Power BI DAX CONCATENATE Function is:

CONCATENATE(string1, string2)

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

CONCAT = CONCATENATE(EmployeeSales[FirstName], EmployeeSales[LastName])
Power BI DAX CONCATENATE function 13

Power BI DAX CONCATENATEX Function

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

CONCATENATEX(tableName, ColumnName, Delimiter)

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

CONCATENATEX = CONCATENATEX(EmployeeSales, EmployeeSales[LastName], ",")
Power BI DAX CONCATENATEX function 14

Power BI DAX FIXED Function

The Power BI DAX FIXED function is useful to round the given number to a specified number of digits and returns in text data type. The syntax of the Power BI DAX FIXED Function is:

FIXED(number, decimals, comma)

The below Power BI DAX FIXED function rounds sales decimals to a single digit, and it won’t allow comma

FIXED = FIXED(EmployeeSales[Sales], 1, 1)
Power BI DAX FIXED function 15

Power BI DAX BLANK Function

The Power BI DAX BLANK function is useful to return a blank. You can also use this to check whether the crows have any blanks or not. The syntax of this Power BI DAX BLANK Function is:

BLANK()

Below If statement checks whether there are any blanks while dividing Sales by Service Grade, if true, 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 BLANK function 16

From the screenshot below, you can see the result.

Power BI DAX BLANK function 17

Power BI DAX UNICODE Function

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

UNICODE(string)

It returns the ASCII value of the first character in the last name column

CODE = UNICODE(EmployeeSales[LastName])
Power BI DAX UNICODE function 18

Power BI DAX COMBINEVALUES Function

The DAX COMBINEVALUES function combines two or more strings using the specified delimiter. The syntax of this Power BI DAX COMBINEVALUES Function is:

COMBINEVALUES(Delimiter, string1, string2,..)

The below statement combines First Name, Last Name, Education columns using a comma delimiter.

CONCATENATEX = CONCATENATEX(EmployeeSales, EmployeeSales[LastName], ",")
Power BI DAX COMBINEVALUES function 19

Power BI DAX FORMAT Function

The Power BI DAX FORMAT function format numbers and dates to predefined formats. The syntax of the Power BI DAX FORMAT Function is:

FORMAT(expression, format_type)

The below Power BI DAX FORMAT function formats the Sales amount into currency type

FORMAT = FORMAT(EmployeeSales[Sales], "Currency")
Power BI DAX FORMAT function 20

Power BI DAX VALUE Function

The DAX VALUE function converts the string numbers into a number. The syntax of the Power BI DAX VALUE Function is:

VALUE(string)

The below statement converts the string numbers in Fixed function result to a number

VALUE = VALUE(EmployeeSales[FIXED])
Power BI DAX VALUE function 21

Placed Under: Power BI

  • Install Power BI Desktop
  • Connect Power BI to SQL Server
  • Add Rename and Duplicate Pages
  • Connect to Multiple Excel Sheets
  • Get Excel Data to Power BI
  • Get Text File Data to Power BI
  • Load Data from Multiple Sources
  • Remove Power BI Table Columns
  • Create Power BI Bins
  • Power BI Joins
  • Change Column Data Types
  • Combine Multiple Tables
  • Power BI Clusters
  • How to Enter Data into Power BI
  • How to Format Dates in Power BI
  • Create Power BI Groups
  • Create Power BI Query Groups
  • Create Power BI Hierarchy
  • Power BI Pivot Table
  • Power BI Unpivot Table
  • Rename Power BI Table Names
  • Rename power bi column names
  • Split Columns in Power BI
  • Remove reorder power bi column
  • Power BI Area Chart
  • Format Power BI Area Chart
  • Power BI Bar Chart
  • Format Power BI Bar Chart
  • Power BI Clustered Bar Chart
  • Clustered Column Chart
  • Create a Power BI Card
  • Format Power BI Card
  • Create Power BI Multi-Row Card
  • Format Power BI Multi-Row Card
  • Power BI Column Chart
  • Format Power BI Column Chart
  • Power BI Donut Chart
  • Format Power BI Donut Chart
  • Power BI Funnel Chart
  • Format Power BI Funnel Chart
  • Power BI Heat Map
  • Power BI Line Chart
  • Format Power BI Line Chart
  • Line & Clustered Column Chart
  • Format Line & Clustered Column
  • Line & Stacked Column Chart
  • Format Line & Stacked Column
  • Power BI Matrix
  • Format Power BI Matrix
  • Power BI Table
  • Format Power BI Table
  • Add Data Bars to Power BI Table
  • Add alternate Table Row Colors
  • Power BI Pie Chart
  • Format Power BI Pie Chart
  • Power BI Ribbon Chart
  • Format Power BI Ribbon Chart
  • Power BI Treemap
  • Format Power BI TreeMap
  • Power BI Scatter Chart
  • Format Power BI Scatter Chart
  • Power BI Stacked Area Chart
  • Power BI Stacked Bar Chart
  • Format Stacked Bar Chart
  • Power BI Stacked Column Chart
  • Format Stacked Column Chart
  • Power BI Waterfall Chart
  • Format Power BI Waterfall Chart
  • Power BI 100% Stacked Bar Chart
  • 100% Stacked Column Chart
  • Create a Power BI Map
  • Format Power BI Map
  • Power BI Filled Map
  • Format Power BI Filled Map
  • Power BI Basic Filters
  • Power BI Slicer
  • Power BI Top 10 Filters
  • Power BI Drill through Filters
  • Power BI Filters on Measures
  • Power BI Advanced Filters
  • Power BI Report Level Filters
  • Power BI Page Level Filters
  • Power BI Calculated Columns
  • Add Conditional Column
  • Create Power BI Custom Column
  • Power BI Calculated Measures
  • Power BI Calculated Tables
  • Power BI DAX String Functions
  • Power BI DAX Math Functions
  • DAX Trigonometric Functions
  • Power BI DAX Date Functions
  • Power BI DAX Logical Functions
  • Power BI DAX Agg. Functions
  • Power BI Dashboard Introduction
  • Power BI Dashboard Settings
  • Power BI Dashboard Actions
  • Delete Power BI Dashboard
  • Subscribe Power BI Dashboard
  • Add Web Content to Dashboard
  • Add Image to Dashboard
  • Add video to power bi dashboard
  • Add Title to Power BI Dashboard
  • Pin Workbooks to Dashboard
  • Register to Power BI Service
  • Connect to Power BI Service
  • Create Power BI Workspace
  • Share Power BI Workspace
  • Create a Report in Workspace
  • Rename a Report in Workspace
  • Rename a Dataset in Workspace
  • Quick insights in Power BI
  • Get insights in Power BI
  • Publish power bi desktop report
  • Upload Power BI Workbooks
  • Upload Excel Files to Dashboard
  • Edit Power BI App
  • View Published Power BI App
  • Publish Power BI App
  • Share Power BI Dashboard
  • Share Power BI Workspace
  • Share Power BI Report
  • Power BI Q&A
  • Power BI Q&A Advanced
  • Customize Q&A Suggestions
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy