SUBSTITUTEWITHINDEX DAX Function (Table manipulation)
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.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
|
Table Iterator |
Table to be modified. |
|
| Name |
A name of the column to be added to the first table. |
|
| SemiJoinIndexTable |
Table that will be ordered and used to calculate index and to join with the first argument. |
|
|
Expression Row Context |
Repeatable |
Order by expression for the second parameter (SemiJoinIndexTable). |
| Order | Optional Repeatable |
The order to be applied. 0/FALSE/DESC – descending; 1/TRUE/ASC – ascending. |
Return values
A table which includes only those values present in the indexColumnsTable table and which has an index column instead of all columns present (by name) in the indexColumnsTable table.
Examples
-- SUBSTITUTEWITHINDEX is a tool function used mainly by
-- Power BI to map values in a query to column in a matrix
-- by substituting the index columns with a number indicating
-- the column number where to put the result.
-- The matching between the two tables is based on column names.
DEFINE
VAR R =
SUMMARIZECOLUMNS (
'Product'[Brand],
'Date'[Calendar Year],
TREATAS ( { "Contoso", "Fabrikam" }, 'Product'[Brand] ),
"Amt", [Sales Amount]
)
VAR C =
SUMMARIZE ( Sales, 'Date'[Calendar Year] )
VAR Result =
SUBSTITUTEWITHINDEX ( R, "Column #", C, 'Date'[Calendar Year], ASC )
EVALUATE R
EVALUATE C
EVALUATE Result
| Brand | Calendar Year | Amt |
|---|---|---|
| Contoso | 2007-01-01 | 2,729,818.54 |
| Fabrikam | 2007-01-01 | 1,652,751.34 |
| Contoso | 2008-01-01 | 2,369,167.68 |
| Fabrikam | 2008-01-01 | 1,993,123.48 |
| Contoso | 2009-01-01 | 2,253,412.80 |
| Fabrikam | 2009-01-01 | 1,908,140.91 |
| Calendar Year |
|---|
| 2007-01-01 |
| 2008-01-01 |
| 2009-01-01 |
| Brand | Amt | Column # |
|---|---|---|
| Contoso | 2,729,818.54 | 0 |
| Fabrikam | 1,652,751.34 | 0 |
| Contoso | 2,369,167.68 | 1 |
| Fabrikam | 1,993,123.48 | 1 |
| Contoso | 2,253,412.80 | 2 |
| Fabrikam | 1,908,140.91 | 2 |
Last update: Oct 22, 2021 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/substitutewithindex-function-dax