MID

How to Use the Power BI DAX function MID

What is the MID Function?

The MID function is a string function that is used to extract a substring from a given text string. The function takes three arguments: the text string, the starting position of the substring, and the number of characters to extract. For example, if we have a text string “Power BI DAX function MID” and we want to extract “BI”, we can use the MID function with the starting position of 7 and the number of characters to extract of 2.

How Does the MID Function Work?

The MID function works by taking a text string and extracting a substring from it based on the starting position and the number of characters to extract. To use the function, we need to specify the text string, the starting position, and the number of characters to extract.

Let’s take a look at the syntax of the MID function:

MID(text, start_num, num_chars)

- **text**: The text string that we want to extract the substring from.

- **start_num**: The starting position of the substring that we want to extract. This can be a number or a reference to a cell that contains a number.

- **num_chars**: The number of characters that we want to extract from the text string. This can be a number or a reference to a cell that contains a number.

How to Use the MID Function in Power BI

To use the MID function in Power BI, we first need to create a new measure or calculated column. We can do this by going to the Modeling tab and clicking on the New Measure or New Column button.

Once we have created a new measure or calculated column, we can use the MID function to extract a substring from a text string. Let's take a look at some examples.

Example 1: Extracting a Substring with Known Starting Position and Number of Characters

Suppose we have a column in our data table called "Product Name" that contains the name of the product along with its SKU number. For example, "Product A (SKU1234)". We want to extract only the SKU number from this column.

We can use the MID function to achieve this by specifying the starting position of the SKU number and the number of characters to extract. In this case, the starting position of the SKU number is 11 (the position of the first digit of the SKU number) and the number of characters to extract is 4 (the length of the SKU number).

=MID([Product Name], 11, 4)

This will extract the SKU number from the "Product Name" column.

Example 2: Extracting a Substring with Unknown Starting Position and Number of Characters

Suppose we have a column in our data table called "Description" that contains a long text string. We want to extract the first sentence from this column.

We can use the MID function to achieve this by finding the position of the first period in the text string and extracting the substring up to that position.

=MID([Description], 1, FIND(“.”, [Description]))

This will extract the first sentence from the "Description" column.

The Power BI DAX function MID is a powerful tool that can be used to extract a substring from a text string. It is a very useful function that can help us manipulate and transform data in Power BI. In this article, we have explored what the MID function is, how it works, and how to use it in Power BI. We hope that this article has been helpful in understanding the MID function and how it can be used in Power BI.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)