Daily DAX : Day 245 MID

 The MID function in Power BI DAX (Data Analysis Expressions) is used to extract a substring from a text string, starting at a specified position and for a specified number of characters. It is similar to the MID function in Excel and is useful for text manipulation in data models.

Syntax

MID(<text>, <start_position>, <length>)

Parameters

    <text>: The text string from which you want to extract a substring. This can be a column reference or a text value.

    <start_position>: The position in the text string where the extraction begins (1-based indexing, meaning the first character is at position 1).

    <length>: The number of characters to extract starting from the <start_position>.

Return Value

A string containing the specified number of characters from the given text, starting at the specified position.

Use Case

The MID function is commonly used when you need to extract a specific portion of a text string, such as:

    Extracting a part of a product code, order number, or other structured text.

    Parsing fixed-length or semi-structured data.

    Cleaning or reformatting text data for reporting or analysis.

Example

Suppose you have a column called ProductCode in your dataset with values like ABC12345XYZ. You want to extract the middle 5 characters (12345) starting from position 4.

DAX Formula

ExtractedCode = MID('Table'[ProductCode], 4, 5)

Explanation

    'Table'[ProductCode]: Refers to the column containing the text (e.g., ABC12345XYZ).

    4: Starts extracting from the 4th character (1-based index).

    5: Extracts 5 characters.

Result

For ProductCode = ABC12345XYZ, the result would be 12345.

Practical Use Case

Scenario: You have a dataset with a column OrderID containing values like ORD20231025US. You want to extract the year (2023) from positions 4 to 7 to analyze orders by year.

DAX Formula

OrderYear = MID('Orders'[OrderID], 4, 4)

Result

For OrderID = ORD20231025US, the result is 2023. You can then use this extracted year for further analysis, such as grouping sales by year.

Notes

    Error Handling: If <start_position> is less than 1 or greater than the length of the text, or if <length> is negative, the function may return an error or unexpected results.

    Alternative Functions: Combine MID with other DAX functions like LEFT, RIGHT, FIND, or LEN for more complex text manipulations.

    Performance: MID is straightforward and efficient for text extraction but ensure the input column is of text data type to avoid errors.

Example with Combined Functions

If you need to extract a dynamic portion of a string based on a delimiter, you might combine MID with FIND. For instance, to extract the text between two hyphens in PART-XYZ123-USA:

DAX Formula

ExtractedPart = MID('Table'[Code], FIND("-", 'Table'[Code]) + 1, 

                    FIND("-", 'Table'[Code], FIND("-", 'Table'[Code]) + 1) - FIND("-", 'Table'[Code]) - 1)

Result

For Code = PART-XYZ123-USA, the result is XYZ123.

This makes MID a versatile function for text parsing in Power BI when dealing with structured or semi-structured data.