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.
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.
- Open Power BI Desktop.
- On the page canvas, you will find “Try a sample dataset”. Click in this.
- From the pop-up box, click on “Load sample data” and select the “financials” sheet for the analysis.
- Click on Load and save data.
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.
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.
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.
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.
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.
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.
Use of RIGHT function
The RIGHT function returns the last character or characters in a text string, based on the number of characters specified.
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.
MID(<text>, <start_num>, <num_chars>)
Please find the code in the below location
DAX — Chapter 15
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.