Create table from existing one with the condition from the user

[Tomas930115]

Actual =

VAR x = variables[var_2021]

VAR y = variables[var_2022]

VAR z = variables[var_RE]

VAR a = variables[var_BP23]

RETURN

SWITCH(

    TRUE(),

    [User_selection_actual] = variables[var_Slicer_option_Actual_1], CALCULATETABLE(query_all,query_all,query_all[Version]=x),

    [User_selection_actual] = variables[var_Slicer_option_Actual_2], CALCULATETABLE(query_all,query_all,query_all[Version]=y),

    [User_selection_actual] = variables[var_Slicer_option_Actual_3], CALCULATETABLE(query_all,query_all,query_all[Version]=z),

    [User_selection_actual] = variables[var_Slicer_option_Actual_4], CALCULATETABLE(query_all,query_all,query_all[Version]=a),

    "Select"

)

Hi, 

I am trying to create a new table from the existing table and it should take the condition from the user. So for instance, the user would pick Slicer_option_actual_1 and then it would create a new table that would filter from the old table the correct version of " Slicer_option_actual_1".

The error is: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I have googled this problem but could nto find it for the instance where the condition is based on the user. 

Thank you for your help

[amitchandak]

@Tomas930115 , You can not create a table based on the selected value. As that will not be correct

For error

This may measure where are using that code

or you need a max here

[User_selection_actual] = max(ariables[var_Slicer_option_Actual_1])

[Tomas930115]

I will illustrate the problem in this example:

Original Table:

Version	Revenue	Product
x	10
x	10
x	10
x	10
z	20
z	20
y	30
a	40

On PowerBi dashborad, I the user would select "version" for instance -> "x" and the new table would be created only with a rows version "x" 

New table:

Version	Revenue	Product
x	10
x	10
x	10
x	10

[v-stephen-msft · Accepted solution]

Hi @Tomas930115 ,

If you want a table visual displayed, it could be achieved.

If you want a calculated table created by dax, as @amitchandak  said, it's impossible.

Because calculated tables are static results, results are not dynamically updated because of slicer or filter filtering.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.