Change column reference in dax formula based on slicer selection.

[Anonymous]

Hi, 

I'm having trouble with changing a column reference based on a slicer selection. 

The videos and questions, i've seen use SELECTEDVALUES and SWITCH(TRUE() to return an expression. 

but i cant seem to return a different slicer selection. 

The measure i've trying to make change is 

At the moment the formula, i have is this

Cumulative Growth =

EXP(

SUMX(

FILTER(ALL(Rates), Rates[Year]<=MAX(Rates[Year])),

LN(1+Rates[ Index 1 ])

)

),

Based on selection of this table
IndexSelected

Index 1

Index 2

Index 3

each index is a column in the 'Rates' table

Can i make the Index column reference a variable and change based on the value selected in a slicer? 

many thanks

[MFelix · Accepted solution]

Hi @Anonymous ,

You need to redo your measure to the following:

Cumulative Growth =
EXP (
    SUMX (
        FILTER ( ALL ( Rates ), Rates[Year] <= MAX ( Rates[Year] ) ),
        VAR Index_Selection =
            SELECTEDVALUE ( Table[IndexSelected] )
        RETURN
            LN (
                1
                    + SWITCH (
                        Index_Selection,
                        1, Rates[ Index 1 ],
                        2, Rates[ Index 2 ],
                        3, Rates[ Index 3 ]
                    )
            )
    )
)

If this does not work can you share a small sample of your data and expected result

[Anonymous]

Thanks, 

helped a lot.