return table from switch

[vfx661]

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.

[Anonymous · Accepted solution]

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 Chang

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

[bhanu_gautam]

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

[vfx661]

Hi @bhanu_gautam, I've tried your approach with SWITCH and it gave the same error as before.  Perhaps you meant something else?