Forum Discussion
return table from switch
If I set a variable to be a table row, I can get values from this row like this
(ignore my_level here, it becomes relevant below):
my_level = "B"
my_row = TOPN(1, my_table_B)
my_value = MAXX(my_row, [my_col])
Where my_table_B is a table associated with level = "B".
However, I'd like to switch between multiple tables, like this
my_level = "B"
my_row = SWITCH(
my_level,
"A": TOPN(1, my_table_A),
"B": TOPN(1, my_table_B)
)
my_value = MAXX(my_row, [my_col])
This doesn't work - DAX doesn't recognise my_row as a table. In practice I have many levels so doing it the 'hard coded' way would be very messy.
Hi vfx661 ,
Maybe I misunderstood. But I think you are complicating the issue. You are using TOPN to get the max or min value and then you need to get that row of data, why don't you just use MAXX or MINX?You can check the following results to get Value or ID:
Measure = var _s = SELECTEDVALUE('Table'[Table]) RETURN SWITCH(_s,"A",MAXX('Table A',[Value]),"B",MAXX('Table B',[Value]))An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
vfx661 , In Power BI, the SWITCH function does not return a table, which is why your approach is not working. Instead, you can use a combination of IF statements or SWITCH within a VAR
VAR my_level = "B"
VAR my_row = SWITCH(
TRUE(),
my_level = "A", TOPN(1, my_table_A),
my_level = "B", TOPN(1, my_table_B),
-- Add more conditions here for other levels
BLANK()
)
VAR my_value = MAXX(my_row, [my_col])
RETURN my_valueHi vfx661 ,
Maybe I misunderstood. But I think you are complicating the issue. You are using TOPN to get the max or min value and then you need to get that row of data, why don't you just use MAXX or MINX?You can check the following results to get Value or ID:
Measure = var _s = SELECTEDVALUE('Table'[Table]) RETURN SWITCH(_s,"A",MAXX('Table A',[Value]),"B",MAXX('Table B',[Value]))An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
