Use slicer to dynamically select column

Question

How can I use a slicer to dynamically select a column from my table? I want to have two slicers: the first to select the function and the second to select the column. I can get the first slicer working, but having issues with the second.

I’ve tried

    FinalValue = 
    VAR Func = SELECTEDVALUE(StatFunctionSelector[Function])
    VAR Col = SELECTEDVALUE(FieldParameter[Parameter])

RETURN SWITCH(Func, 
"Average", AVERAGE(Table1[Col]), 
"Max", MAX(Table1[Col]), 
"Min", MIN(Table1[Col]),
"Sum", SUM(Table1[Col]), 
"Count", COUNT(Table1[Col]), 
"StdDev", STDEV.P(Table1[Col]))

I’d rather avoid having to spell each permutation out explicitly.

For example, if I have columns

A,B,C,D,E,F,G

I’d like to have to a slider that lets you select which function to apply (average, min, max, etc.) and then another slicer that selects which column to use.

For example, if selected “Average” and “A”, I’d get the average of column A.

@ttruhcheva I tried using a field parameter, but can’t get it to work in my measure. — Jack
– Jack, Commented Aug 11, 2024 at 16:27

Nick A · Accepted Answer · 2024-08-12 15:04:24Z

Unfortunately, it is not possible to refer to a column using a string in DAX. So you would need to spell out each permutation like the following measure:

Measure = 
VAR Func = SELECTEDVALUE(StatFunctionSelector[Function])
VAR Col = SELECTEDVALUE(StatColumnSelector[Column])

VAR output = 
SWITCH( Col,
"A", SWITCH(Func, 
    "Average", CALCULATE(AVERAGE(Table1[A])),
    "Max", CALCULATE(MAX(Table1[A])),
    "Min", CALCULATE(MIN(Table1[A])),
    "Sum", CALCULATE(SUM(Table1[A])),
    "Count", CALCULATE(COUNT(Table1[A])),
    "StdDev", CALCULATE(STDEV.P(Table1[A])),
    BLANK()),
"B", SWITCH(Func, 
    "Average", CALCULATE(AVERAGE(Table1[B])),
    "Max", CALCULATE(MAX(Table1[B])),
    "Min", CALCULATE(MIN(Table1[B])),
    "Sum", CALCULATE(SUM(Table1[B])),
    "Count", CALCULATE(COUNT(Table1[B])),
    "StdDev", CALCULATE(STDEV.P(Table1[B])),
    BLANK())

)

RETURN output

StatFunctionSelector Table:

StatFunctionSelector = 
DATATABLE(
    "Function", STRING,
    {
        {"Average"},
        {"Max"},
        {"Min"},
        {"Sum"},
        {"Count"},
        {"StdDev"}
    }
)

StatColumnSelector Table:

StatColumnSelector = 
DATATABLE(
    "Column", STRING,
    {
        {"A"},
        {"B"},
        {"C"},
        {"D"}
    }
)

Collectives™ on Stack Overflow

Use slicer to dynamically select column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related