cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TTKL
Helper I
Helper I

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
MFelix
Super User
Super User

Hi @TTKL ,

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @TTKL ,

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Thanks, 

helped a lot. 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!