cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
MFelix
Super User
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


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 @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


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

Anonymous
Not applicable

Thanks, 

helped a lot. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.