Power BI: Text Functions

DAX (Data Analysis Expressions) is a formula language used in Power BI to create custom calculations and data analysis. DAX functions are an essential part of creating these calculations, and there are several categories of DAX functions, including Text Functions.

Text functions are used to manipulate text data in Power BI. They allow users to modify, extract, and format text data within a data model. In this article, we will discuss some of the most commonly used Text Functions in DAX.

CONCATENATE()

The CONCATENATE() function is used to combine two or more text strings into a single string. The syntax for the CONCATENATE() function is:

CONCATENATE(<text1>, <text2>, ...)

Where <text1>, <text2>, ... are the text strings you want to concatenate. The function can accept up to 255 arguments.

LEFT()

The LEFT() function is used to extract a specified number of characters from the beginning of a text string. The syntax for the LEFT() function is:

LEFT(<text>, <num_chars>)

Where <text> is the text string you want to extract characters from, and <num_chars> is the number of characters you want to extract.

RIGHT()

The RIGHT() function is similar to the LEFT() function, but it extracts characters from the end of a text string. The syntax for the RIGHT() function is:

RIGHT(<text>, <num_chars>)

Where <text> is the text string you want to extract characters from, and <num_chars> is the number of characters you want to extract.

SUBSTITUTE()

The SUBSTITUTE() function replaces a specified text string within a larger text string with a new text string. The syntax for the SUBSTITUTE() function is:

SUBSTITUTE(<text>, <old_text>, <new_text>, [<instance_num>])

Where <text> is the text string you want to replace a substring in, <old_text> is the substring you want to replace, <new_text> is the new text you want to replace it with, and [<instance_num>] is the instance number of the substring you want to replace (if there are multiple instances in the text string).

LEN()

The LEN() function is used to return the number of characters in a text string. The syntax for the LEN() function is:

LEN(<text>)

Where <text> is the text string you want to count the number of characters in.

LOWER() and UPPER()

The LOWER() function converts all characters in a text string to lowercase, while the UPPER() function converts all characters to uppercase. The syntax for these functions is:

1. LOWER(<text>)
2. UPPER(<text>)

Where <text> is the text string you want to convert the case of.

The TRIM() function removes all leading and trailing spaces from a text string. The syntax for the TRIM() function is:

TRIM(<text>)

Where <text> is the text string you want to remove spaces from.

The other important functions under Text are as follows:

• COMBINEVALUES
• FORMAT
• LEFT
• MID
• REPLACE
• RIGHT
• SEARCH
• SUBSTITUTE
• TRIM
• UNICHAR

In conclusion, Text Functions are an important part of the DAX formula language in Power BI. These functions allow users to manipulate text data within a data model, which is essential for creating custom calculations and analysis. By understanding and utilizing these functions, users can create more accurate and efficient data models in Power BI.

C