This blog is an introductory guide to how to use DAX (Data Analysis Expressions) for text functions within Power BI Desktop. This is to be used to create calculated columns, which are columns created by the user that are calculated from a row level. DAX can also be used to create measures, which are used within the visualisation which is evaluated in the context of the report. Additionally, we use DAX for tables or parameters.
This and the worked examples are based on what I learned from Robbin Vernooij! *pause for applause as it is so clear!*
Before we start, here is a Cheat Code of text functions to remember for DAX:
- LEFT(text, num_chars): This will return a number of characters from the left side of a string.
- RIGHT(text, num_chars): This will return a number of characters from the right side of a string.
- MID(text, start_num, num_chars): This will return a number of characters from the string.
- LEN(text): This will return the number of characters within a text string.
- LOWER(text): This will change all the characters within a string to lowercase.
- UPPER(text): This will change all the characters within a string to uppercase.
- TRIM(text): This will remove the leading and trailing spaces within the string.
- CONCATENATE(text1, text2, etc): This combines different strings together into one string.
- SUBSTITUTE(text, old_text, new_text, [instance_num]): This will replace a specified part of the text within the string with different text at each occurrence.
- FIND(find_text, within_text, [start_num]): This will give the start position of a string which is within another text string.
Here is a worked example which shows use cases for these functions: Create a Full Name Column
- First, navigate to 'Table Tools' in the dynamic ribbon bar at the top. Here you can find the calculation tabs that we will be using.
2. In this task, there are 2 columns for the names, we want to add a New Column and use DAX code to create a full name column.
3. Here is an example of DAX you can use
Hope this was useful! Have a go exploring other DAX syntax for other uses too.