Daily DAX : Day 395 SUBSTITUTEWITHINDEX

Power BI DAX: SUBSTITUTEWITHINDEX Advanced

Overview

The SUBSTITUTEWITHINDEX function is a powerful text manipulation function in Power BI DAX, introduced to efficiently replace portions of a string using an index-based lookup table.

It allows you to perform multiple, dynamic substitutions in a single operation — far more efficiently than nested SUBSTITUTE functions.

Syntax

SUBSTITUTEWITHINDEX (
    <expression>,
    <search_table>,
    <index_column>,
    <replacement_column>
    [, <occurrence>]
)
  • <expression>: The original text string to modify.
  • <search_table>: A table containing search and replacement values.
  • <index_column>: Column in the table with values to find in the expression.
  • <replacement_column>: Column with replacement values.
  • <occurrence> (Optional): Specifies which occurrence to replace (1 = first, 2 = second, etc.).

Use Case: Dynamic Product Code Translation

Imagine you have product codes like P001, P002 embedded in descriptions, and you want to replace them with full product names dynamically.

Step 1: Create a Mapping Table

Code (Index) ProductName (Replacement)
P001Laptop Pro 15"
P002Wireless Mouse
P003USB-C Hub

Step 2: Use SUBSTITUTEWITHINDEX

Translated Description = 
SUBSTITUTEWITHINDEX(
    Products[RawDescription],
    ProductMapping,
    ProductMapping[Code],
    ProductMapping[ProductName]
)

Result Example

Input: "Order includes P001 and P003"

Output: "Order includes Laptop Pro 15" and USB-C Hub"

Advanced Use Case: Multi-Occurrence Control

Use the optional occurrence parameter to replace only specific instances:

Replace First Only = 
SUBSTITUTEWITHINDEX(
    [Text],
    LookupTable,
    LookupTable[Find],
    LookupTable[Replace],
    1  // Only replace first match per row
)

Benefits vs Nested SUBSTITUTE

Why use SUBSTITUTEWITHINDEX?

  • Scalable: Add new mappings without changing DAX.
  • Performance: Faster than 10+ nested SUBSTITUTE() calls.
  • Maintainable: Centralize replacements in a table.
  • Dynamic: Works with filtered or related tables.

Limitations & Tips

  • Search is case-sensitive.
  • Requires a table — not compatible with scalar lists.
  • Use TRIM and UPPER/LOWER for case-insensitive matching.
  • Best for structured replacements (codes, IDs, abbreviations).


Comments

Post a Comment

Popular posts from this blog

Daily DAX : Day 131 SELECTEDMEASURE

 The SELECTEDMEASURE function in Power BI's DAX (Data Analysis Expressions) language is a powerful tool used primarily within calculation groups to dynamically reference the measure currently being evaluated in a report. It’s part of the calculation group feature introduced to streamline complex reporting scenarios and improve maintainability by reducing the need to duplicate measure logic. What is SELECTEDMEASURE? SELECTEDMEASURE is a DAX function that returns a reference to the measure that is currently active or "selected" in the context of a calculation group. It doesn’t take any arguments and is typically used in the expression of a calculation item within a calculation group. This allows you to write generic logic that can dynamically apply to whichever measure is being evaluated in a visual, without hardcoding specific measure names. Syntax SELECTEDMEASURE()     Returns: A reference to the measure currently being evaluated in the report context. Use Case SELECTEDME...
Read more

Daily DAX : Day 446 INFO.CSDLMETADATA

Power BI DAX Function: INFO.CSDLMETADATA INFO.CSDLMETADATA` is a specialized DAX function introduced as part of a broader set of "INFO" functions (like `INFO.TABLES` or `INFO.MEASURES`) used for **model introspection**. It allows you to programmatically access the underlying structure of your Power BI semantic model. What does it do? The function returns a table with a single column containing the Conceptual Schema Definition Language (CSDL)  metadata of your model in XML format . In plain English, it exports a highly technical "blueprint" of your entire data model—including tables, columns, relationships, and measures—as a structured XML string. It is essentially the DAX equivalent of the `DISCOVER_CSDL_METADATA` Dynamic Management View (DMV) used in Analysis Services. --- Syntax and Output The syntax is straightforward as it requires no arguments: ```dax EVALUATE INFO.CSDLMETADATA() ``` Return Value : A table with one column and one row. Content : A long string ...
Read more

Daily DAX : Day 453 ENDOFWEEK

Power BI DAX Function: ENDOFWEEK Description The ENDOFWEEK function is a Time Intelligence function in DAX (Data Analysis Expressions) used in Power BI. It returns the date that represents the end of the week containing a specified date (typically Saturday, as weeks are considered to start on Sunday by default in DAX time intelligence). Syntax ENDOFWEEK( [, ]) : A column reference (usually from a Date table) or a date expression. Required. : Optional. Specifies the week-ending convention. Default is Saturday if omitted. Examples 1. Basic usage (default: week ends on Saturday): End of Week = ENDOFWEEK('Date'[Date]) For a date like January 8, 2026 (Thursday), this would return January 10, 2026 (Saturday). 2. In a measure for cumulative sales up to end of week: Total Sales to End of Week = CALCULATE( SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], ST...
Read more