How to Use 7+Text Functions in DAX — DAX in Power BI — Chapter 15

We are going to explore CONCATENATE, CONCATENATEX, COMBINEVALUES, EXACT, FIND, SEARCH, LEFT, RIGHT and MID Text functions in DAX using practical examples.


In any data analysis project, the text functions of DAX are playing very important roles. Using these functions we can fulfil some customer requirements which are related to the data visualization perspective. 

In this blog, you are going to explore how you can use different DAX text functions like CONCATENATE, CONCATENATEX, COMBINEVALUES, EXACT, FIND, SEARCH, LEFT, RIGHT and MID.

Get Data

This time we will use available sample data which is already available in Power BI Desktop. Let me show you how you can do that. 

  1. Open Power BI Desktop. 
  2. On the page canvas, you will find “Try a sample dataset”. Click in this.
  3. From the pop-up box, click on “Load sample data” and select the “financials” sheet for the analysis.
  4. Click on Load and save data.
DAX Text Functions in Power BI
Image by Author

Use of CONCATENATE, CONCATENATEX functions

Now time to check two more concatenated functions, which are CONCATENATE and CONCATENATEX. 

CONCATENATE function helps to join two text strings into one string. This function works for only two text strings.

Syntax: CONCATENATE(<text1>, <text2>)

CONCATENATEX function helps to concatenate the result of an expression evaluated for each row in a table and return a text string. This function is not supported in DirectQuery mode.

Syntax:

CONCATENATEX(<table>, <expression>[, <delimiter> [, <orderBy_expression> [, <order>]]…])

Use of COMBINEVALUES functions

To join two or more text values and concatenated to one string, you can use COMBINEVALUES function. This function can be supported by DirectQuery models.

Syntax:

COMBINEVALUES(<delimiter>, <expression>, <expression>[, <expression>]…)

Use of EXACT function

The EXACT function is used to compare two strings and returns TRUE if results are the same, otherwise FALSE. This function is case-sensitive.

Syntax: EXACT(<text1>,<text2>)

Use of FIND function

The FIND function returns the starting point of the text string that you want to find within another text string. Please remember this is case-sensitive.

Syntax:

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

Use of SEARCH function

The SEARCH function returns the number of the character at which a specific character or text string is first found, reading left to right. This is not case-sensitive.

Syntax:

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

 Let us find some examples to test these functions.

Use of LEFT function

The LEFT function returns the specified number of characters from the start of a text string.

Syntax: 

LEFT(<text>, <num_chars>)

Use of RIGHT function

The RIGHT function returns the last character or characters in a text string, based on the number of characters specified.

Syntax:

RIGHT(<text>, <num_chars>)

Use of MID function

The MID function returns a string of characters from the middle of a text string, given a starting position and length.

Syntax:

MID(<text>, <start_num>, <num_chars>)

Download

Please find the code in the below location

DAX — Chapter 15

https://github.com/arpitag1/Power-BI

Conclusion

The blog teaches us the 9 text functions of DAX with practical examples.

If you have any questions related to this project, please feel free to post your comments.

Please visit my website for other technical resources.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: