--  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 data lineage
--  or column names.
DEFINE
    VAR R =
        SUMMARIZECOLUMNS (
            'Product'[Brand],
            'Date'[Calendar Year],
            TREATAS ( { "Contoso", "Fabrikam" }, 'Product'[Brand] ),
            "Amount", [Sales Amount]
        )
    VAR C =
        SUMMARIZE ( Sales, 'Date'[Calendar Year] )
    VAR C_ColumnName =
        SELECTCOLUMNS({"CY 2007", "CY 2008", "CY 2009"}, "Calendar Year", [Value])
    VAR Result =
        SUBSTITUTEWITHINDEX ( R, "Column #", C, [Calendar Year], ASC )

EVALUATE R

EVALUATE C

EVALUATE C_ColumnName

EVALUATE Result

-- The following code would generate an error 
-- because C_ColumnName has the same name
-- of a column with a data lineage
-- EVALUATE
-- SUBSTITUTEWITHINDEX ( R, "Column #", C_ColumnName, [Calendar Year], ASC )

-- The following code works because the 
-- Date[Calendar Year] column loses the data lineage
-- in SELECTCOLUMNS by using an expression
EVALUATE
SUBSTITUTEWITHINDEX ( 
    SELECTCOLUMNS ( 
        R, 
        'Product'[Brand],
        -- the expression remove the data lineage
        "Calendar Year", 'Date'[Calendar Year] & "",
        [Amount]
    ),
    "Column #", C_ColumnName, [Calendar Year], ASC
)