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: May 24, 2022 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/substitutewithindex-function-dax