DAX Function Guide

SUBSTITUTEWITHINDEX
Empty image or helper icon

Sam McKay

CEO & Founder

How does the SUBSTITUTEWITHINDEX 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?

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

Loading

Considerations when using the SUBSTITUTEWITHINDEX?

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

Loading

Formula examples using the SUBSTITUTEWITHINDEX

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 Courses

Loading