Forum Discussion

vfx661's avatar
vfx661
Advocate I
2 years ago
Solved

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(m...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
bhanu_gautam
Super User
2 years ago

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's avatar
vfx661
Advocate I
2 years ago

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