LEFT(text, number_of_characters)

Here, text is the text string containing the characters you want to extract, and number_of_characters is an optional parameter that indicates the number of characters you want to extract. It defaults to a value of 1 if a value is not provided.

For more details see:

RIGHT(text, number_of_characters)

MID(text, start_number, number_of_characters)

Here, text is the text string containing the characters you want to extract, start_number is the position of the first character you want to extract (positions in the string start at 1), and number_of_characters is the number of characters you want to extract.

For more details see:

Let’s dive deeper with some examples. Let’s say we have a Product Code column within our Product Table. The Product Code takes the form of product_abbreviation-product_num-color_code for example, SM-5933-BK.

We will first extract the first two characters using LEFT, these represent the product abbreviation. We can create a calculated column in the products table using the following expression:

Product Abbrev = LEFT(Products[Product Code], 2)

Product Color = RIGHT(Products[Product Code], 2)

Product Number = MID(Products[Product Code], 4, 4)

Together, these three functions form the cornerstone of text extraction. However, they can sometimes stumble when there are unexpected text patterns. Take, for instance, the last row shown below for Product Code TBL-57148-BLK.

FIND(find_text, within_text[, [start_number][, NotFoundValue]])

Here, find_text is the text string you are seeking, you can use empty double quotes "" to match the first character of within_text. Speaking of, within_text is the text string containing the text you want to find.

The other parameters are optional, start_number is the position in within_text to start the seeking and defaults to 1 meaning the start of within_text. Lastly, NotFoundValue although optional is highly recommended and represents the value the function will use when find_text is not found in within_text, typical values include 0, -1.

SEARCH(find_text, within_text[, [start_number][, NotFoundValue]])

When combined with LEFT, RIGHT, or MID the potential of FIND and SEARCH multiplies, allowing for dynamic text extraction. Let’s consider the problematic Product Code TBL-57148-BLK again.

We started by extracting the product abbreviation with the following expression:

Product Abbrev = LEFT(Products[Product Code], 2)

This works well when all the product codes start with a two-letter abbreviation. But what if they don’t? The fixed number of characters to extract might yield undesirable results. Let’s use FIND to add some much-needed flexibility to this expression.

We know that the product abbreviation is all the characters before the first hyphen. To determine the position of the the first hyphen we can use:

First Hyphen = FIND("-", Products[Product Code])

For TBL-57148-BLK this will return a value of 4. We can then use this position to update our expression to dynamically extract the product abbreviation.

Product Abbrev Dynamic = 
LEFT(
  Products[Product Code], 
  FIND("-", Products[Product Code]) - 1
)

Product Color = RIGHT(Products[Product Code], 2)

This expression assumes that all product color codes are consistently placed at the end and always two characters. However, if there are variations in the length (e.g. TBL-57148-BLK) this method might not be foolproof. To introduce some adaptability, let’s utilize SEARCH.

To determine the position of the last hyphen, since the color code will always be all the characters following this we can use:

Last Hyphen =
SEARCH (
    "-",
    Products[Product Code],
    FIND ( "-", Products[Product Code] ) + 1
)

Here, for the start_number we use the same FIND expression that we did to locate the first hyphen and then add 1 to the position to start the search for the second.

With this position, we can update our Product Color function to account for potential variations:

Product Color Dynamic =
VAR _firstHyphenPosition =
    FIND (
        "-",
        Products[Product Code]
    )
VAR _lastHyphenPosition =
    SEARCH (
        "-",
        Products[Product Code],
        _firstHyphenPosition + 1
    )
RETURN
    RIGHT (
        Products[Product Code],
        LEN ( Products[Product Code] ) - _lastHyphenPosition
    )

The updated calculated column finds the position of the first hyphen, and then uses this position to search for the position of the last hyphen. Once we know the position, we can use this position and the length of the string to determine the number of characters needed to extract the color code ( LEN(Products[Product Code]) - _lastHyphenPosition).

Product Number Dynamic =
VAR _firstHyphenPosition =
    FIND (
        "-",
        Products[Product Code]
    )
VAR _lastHyphenPosition =
    SEARCH (
        "-",
        Products[Product Code],
        _firstHyphenPosition + 1
    )
VAR _productNumberLength = _lastHyphenPosition - _firstHyphenPosition - 1
RETURN
    MID (
        Products[Product Code],
        _firstHyphenPosition + 1,
        _productNumberLength
    )

Position with FIND = FIND("rd", Products[Product Code], 1, -1)

Position with SEARCH = SEARCH("rd", Products[Product Code], -1)

In these example DAX formulas we can see the key difference between FIND and SEARCH. Both calculated columns are looking for “rd” within the Product Code. However, for SM-7818-RD we see FIND does not identify “rd” as a match whereas SEARCH does identify “rd” as a match starting at position 9.

Both of these functions are essential tools in your DAX toolbox, your choice between them will hinge on whether case sensitivity is a factor in your data analysis needs.

By mastering the combination of these text functions, you not only enhance text extraction but also pave the way to advanced text processing with DAX. The intricate synergy of FIND and SEARCH with other functions like LEFT, RIGHT, and MID showcases DAX’s textual data processing potential. Keep reading, as we journey further into more complex and fascinating DAX functions!

CONCATENATE(text1, text2)

The first text string you want to merge is represented by text1, and the second text string to be merged is represented by text2. The text strings can include text, numbers, or you can use a column references.

COMBINEVALUES(delimiter, expression, expression[, expression]...)

The character or set of characters you wish to use as a separator is denoted by delimiter. The expression parameters are the DAX expressions whose value will be joined into the single string.

For more details see:

Let’s put these functions to use and examine some real-world scenarios.

Let’s say we need to create a new Product Label column in the Products table. The label should consist of the product abbreviation directly followed by the product number. We can achieve this using the previously created columns Product Abbrev Dynamic and Product Number Dynamic along with CONCATENATE. The expression for the new column would look like this:

Product Label =
CONCATENATE (
    Products[Product Abbrev Dynamic],
    Products[Product Number Dynamic]
)

Yearly Page Title =
COMBINEVALUES (
    " ",
    YEAR (TODAY ()),
    "- Year to date sales:",
    FORMAT ([YTD Sales TOTALYTD], "Currency")
)

This measure, dynamically generates the title text by combining the current year (Year(TODAY())), the text string “- Year to date sales:”, followed by a YTD measure ([YTD Sales TOTALYTD]).

The [YTD Sales TOTALYTD] measure uses a another set of powerful DAX functions: Time Intelligence functions. For details on the creation of the [YTD Sales TOTALYTD] along with other Time Intelligence functions check out this post that provides an in-depth guide to these functions.

REPLACE(old_text, start_number, number_characters, new_text)

The original text (i.e. the text you want to replace) is represented by old_text. The start_number denotes where, the position in old_text, you want the replacement to begin, and number_characters signifies the number of characters you would like to replace. Lastly, new_text is what you will be inserting in place of the old characters.

SUBSTITUTE(text, old_text, new_text, instance_number)

The main text string where you want the substitution to occur is represented as text, with old_text denoting the existing text string within text that you want to replace. Once the function spots old_text, it replaces it with new_text. If you only wish to change a specific instance of old_text, you can use the optional parameter instance_number to dictate which occurrence to modify. If instance_number is omitted every instance of old_text will be replaced by new_text.

For more details see:

Product Label Color =
REPLACE (
    Products[Product Label],
    LEN ( Products[Product Abbrev Dynamic] ) + 1,
    LEN ( Products[Product Number Dynamic] ),
    Products[Product Color Dynamic]
)
``

Here we are creating a new calculated column Product Label Color which aims to replace the product number with the product color code. The old_text is defined as the column Products[Product Label]. Then, remember the syntax, we need to determine the position to start the replacement (start_number) and the number of characters to replace (number_characters). Since both of these are not consistent we use our previous dynamically determined columns to help.

Here is a break down of the details:

1. Positioning: LEN(Products[Product Abbrev Dynamic])+1 helps find the starting point which is directly following the product abbreviation. LEN counts the length of the Product Abbrev Dynamic column value and adds one, this is then used to start the replacement directly after the abbreviation.
2. Length of Replacement: LEN(Products[Product Number Dynamic]) helps determine the length of the segment we want to replace. It counts how many characters make up the product number, allowing for the product number to vary in length but always be fully replaced.

Lastly, the expression uses the product color code as determined by the Product Color Dynamic column we previously created as the new_text.

Product Label Color Updated =
VAR _initialColorLabel =    
    REPLACE(
        Products[Product Label],
        LEN(Products[Product Abbrev Dynamic])+1,
        LEN(Products[Product Number Dynamic]),
        Products[Product Color Dynamic]
    )

RETURN
SUBSTITUTE(
    SUBSTITUTE(_initialColorLabel, "TBL", "TB"),
    "BLK",
    "BK"
)

Here we start by defining the variable _initialColorLabel using the same expression as the previous example. Then we refine this with a double substitution! The inner SUBSTITUTE (SUBSTITUTE(_initialColorLabel, "TBL", "TB")) takes the initial label as the main text string and searches for TBL and replaces it with TB. The result of this (i.e. TBBLK) is then used as the initial text string for the outer SUBSTITUTE, which searches this string for BLK and replaces it with BK. This produces the new label that is consistent for all products.

Now we are starting to see the true power of DAX Text Functions, this expression does so much more than just a singular adjustment: it first reshapes the product label and then it goes even further to make sure the abbreviation and color codes that make up the label are consistent and concise. It is an illustration of how DAX can incrementally build and refine results for more accurate and streamlined outputs.

Although, these examples are helpful, it may be best to perform these transformations upstream when creating the dynamic extractions. For example the Product Abbrev Dynamic column we created early:

Product Abbrev Dynamic = 
LEFT(
  Products[Product Code], 
  FIND("-", Products[Product Code]) - 1
)

Product Abbrev Dynamic =
SUBSTITUTE (
    LEFT (
        Products[Product Code],
        FIND ( "-", Products[Product Code] ) - 1
    ),
    "TBL",
    "TB"
)

For instance, in our Product Color Dynamic calculated column LEN was instrumental in determining the appropriate number of characters for the RIGHT function to extract. Similarly, our Yearly Page Title measure makes use of FORMAT, to ensure an aesthetic presentation of the YTD Sales value.

These instances highlight the versatility of LEN and FORMAT, illustrating how they can be combined with other DAX functions to achieve intricate data manipulations and presentation. Let’s take a look at the details of these two essential functions.

LEN Function: provides a straightforward way to understand the length of a text string and the syntax couldn’t be more simple.

LEN(text)

The text string you wish to know the length of is represented by “, just pass this to LEN to determine how many characters make up the string. The text string of interest could also be a column reference.

Product Color Dynamic =
VAR _firstHyphenPosition =
    FIND (
        "-",
        Products[Product Code]
    )
VAR _lastHyphenPosition =
    SEARCH (
        "-",
        Products[Product Code],
        _firstHyphenPosition + 1
    )
RETURN
    RIGHT (
        Products[Product Code],
        LEN ( Products[Product Code] ) - _lastHyphenPosition
    )

Product Label Color =
REPLACE (
    Products[Product Label],
    LEN ( Products[Product Abbrev Dynamic] ) + 1,
    LEN ( Products[Product Number Dynamic] ),
    Products[Product Color Dynamic]
)

FORMAT(value, format_string[, locale_name])

Here, value represents the data value or expression that evaluates to a single value you intend to format and could be a number, date, or duration. The format string, format_string, is the formatting template and determines how the value will be presented. For example a format string of 'MMMM dd, yyyy' would present a value of 2023-05-12 as May 12, 2023. The locale_name is optional and allows you to specify a locale, different regions or countries may have varying conventions for presenting numbers, dates, or durations. Examples include en-US, fi-FI, or it-IT.

Now that you know the details of FORMAT see it put to work by revisiting the Yearly Page Title measure.

Yearly Page Title =
COMBINEVALUES (
    " ",
    YEAR (TODAY ()),
    "- Year to date sales:",
    FORMAT ([YTD Sales TOTALYTD], "Currency")
)

In this example, you see that the format_string uses a value of Currency to format the YTD sales. This is an example of a predefined numeric format. For more details on predefined numeric formats, custom numeric formats, custom numeric format characters, predefined date/time formats, and custom date/time formats visit the documentation here: