cancel
Showing results for
Did you mean: Anonymous
Not applicable

## Change column reference in dax formula based on slicer selection.

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
1 ACCEPTED SOLUTION  Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português 2 REPLIES 2  Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português  Anonymous
Not applicable

Thanks,

helped a lot.   