SUBSTITUTEWITHINDEX Function (DAX)

How does the SUBSTITUTEWITHINDEX function (DAX) work?

The SUBSTITUTEWITHINDEX function (DAX) returns a table which represents the semijoin of two tables supplied and for which the common set of columns are replaced by a 0-based index column. The index is based on the rows of the second table sorted by specified order expressions.

SUBSTITUTEWITHINDEX Formula Syntax

SUBSTITUTEWITHINDEX(
     <table>, <indexColumnName>, <indexColumnsTable>, [<orderBy_expression>, [<order>][, <orderBy_expression>, [<order>]]…]
)

How do you use the SUBSTITUTEWITHINDEX function?

The SUBSTITUTEWITHINDEX function can replace those columns in a row set corresponding to column headers of a matrix by indexes representing their positions. You might find this function useful only if you create a dynamic user interface for querying DAX. In fact, Power BI internally uses SUBSTITUTEWITHINDEX for matrix charts.

Related Blog Posts

Related Support Forum Posts

Less Commonly Used Advanced Table Functions
Dax Calculation question
Dax not working

Considerations when using the SUBSTITUTEWITHINDEX function

Columns in the right/second table supplied which do not exist in the left/first table supplied are not included in the returned table and are not used to join on.

The index starts at 0 (0-based) and is incremented by one for each additional row in the right/second join table supplied. The index is based on the sort order specified for the right/second join table.

This function does not guarantee any result sort order.

Related Video Tutorials

Formula examples using the SUBSTITUTEWITHINDEX function

SUBSTITUTEWITHINDEX (SalesYearCategory, “ColumnINdex”, MatrixColumns, ‘Date'[Calendar Year], ASC )

SUBSTITUTEWITHINDEX ( UNION ( ROW ( “Name”, “Marco”, “Company”, “Sqlbi’, “User”, “marcor” ), ROW ( “Name”, “Alberto”, “Company”, “Sqlbi”, “User”, “hal” ), ROW ( “Name”, “Bob”, “Company”, “Contoso”, “User”, “bob97” ) ), “index”, UNION ( ROW ( “Company”, “Sqlbi”, “Name”, “Alberto” ), ROW ( “Company”, “Contoso”, “Name”, “Bob”), ROW ( “Company”, “Contoso”, “Name”, “BOB” ), ROW ( “Company”, “Sqlbi”, “Name”, “Marco” )

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI
Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership