Dax Function: MID

Category: Text Functions

The MID function in Power BI is a DAX (Data Analysis Expressions) function used to extract a specific number of characters from a text string, starting at a specified position. This function is useful for text manipulation and data parsing.

Purpose

• Text Extraction: Extract a portion of text from a string.
• Data Parsing: Isolate key data segments from structured or semi-structured text fields.
• Standardization: Process strings for consistency and formatting.

Type of Calculations

• Performs substring operations on text values.
• Extracts characters based on user-defined start position and length.

Practical Use Cases

1. Extracting area codes from phone numbers.
2. Isolating product codes or identifiers from composite text fields.
3. Parsing dates, times, or specific details from formatted strings.

How Does MID Dax Function Works

• The MID function evaluates the input text.
• It locates the starting position (start_position), which is 1-based (the first character is position 1).
• It extracts a substring of the specified length (num_chars) from the starting position.
• If start_position is out of bounds or num_chars exceeds the text length, adjustments are made:
  • If the starting position exceeds the text length, a blank result is returned.
  • If the requested substring extends beyond the end of the text, only the available characters are extracted.

What Does It Return?

The function returns a text string containing the specified number of characters extracted from the input text. If the input is blank, it returns a blank value.

When Should We Use It?

• Parsing structured data like CSV, JSON, or logs for specific values.
• Extracting segments of strings for formatting, validation, or calculations.
• Cleaning and preparing text fields for reporting or further processing.

Examples

Basic Usage – Extract Part of a Text String:

Result: "BI"

Column Usage – Extract Area Codes:

Extracts the first three characters (area code) from phone numbers.

Advanced Usage – Combine with FIND to Extract Dynamic Substrings:

Extracts the domain from email addresses, assuming a fixed domain length.

Tips and Tricks

• Use FIND or SEARCH to dynamically calculate the start_position when parsing variable-length text.
• Be cautious with 1-based indexing (start position begins at 1, not 0).
• Combine with LEN to ensure the num_chars value doesn’t exceed the length of the text.

Performance Impact of MID DAX Function:

• The function is computationally efficient for most datasets.
• Use in combination with other DAX functions to optimize text processing workflows for large datasets.

Related Functions You Might Need

• LEFT: Extracts characters from the start of a text string.
• RIGHT: Extracts characters from the end of a text string.
• FIND: Locates the position of a substring in a text string.
• LEN: Determines the length of a text string.

Want to Learn More?
For more information, check out the official Microsoft documentation for MID You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.