Forum Discussion

camilocorralesg's avatar
camilocorralesg
Advocate I
3 years ago
Solved

Filter with a dynamic value a virtual table

Hi everybody, I'm trying to Filter a virtual table by Ranking position, the number of positions visualized by the users are chosen from a slicer generated from a What-if parameter. This is my Dax expression:

var agentss = SUMMARIZE(ALL(fct_hotel_revenue),[agent],"__Rank",[Rank], "Income", [Income])
var agentss___ = SELECTCOLUMNS(FILTER(agentss, [__Rank] <= 20), "agent", [agent])
var filterss = FILTER(fct_hotel_revenue, fct_hotel_revenue[agent] IN agentss___)
RETURN
CALCULATE([Income], filterss)
 
Instead of FILTER(agentss, [__Rank] <= 20 I would like to be the dynamic value corresponding to the selected value in the slicer, but when I try it FILTER(agentss, [__Rank] <= SELECTEDVALUE(Ranking[Ranking]) I get blank as a result, how can I fix this?
  • amitchandak's avatar
    amitchandak
    3 years ago

    camilocorralesg , hope Ranking is an independent table with set on number to filter, else use what if parameter

    https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

     

    small change

    var agentss = filter(SUMMARIZE(ALL(fct_hotel_revenue),[agent],"__Rank",[Rank], "Income", [Income]), [__Rank] <= <= SELECTEDVALUE(Ranking[Ranking])  )
    var agentss___ = SELECTCOLUMNS(agentss, "agent", [agent])
    var filterss =
    RETURN
    CALCULATE([Income], FILTER(fct_hotel_revenue, fct_hotel_revenue[agent] IN agentss___))

2 Replies