Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic Top N

Greetings,   I have seen several examples with a dynamic top N but I just can't seem to get it working in my report. Could someone have a look at my example ?    I have created below table  for ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Assume you have measure on which you want rank 

     

    measure =CALCULATE(COUNT(Sheet1[Business_Process_Type]),filter(Sheet1,Sheet1[Category]=1))

     

    Rank you want 

     

    rank = rankx(allselected(Sheet1[Business_Process_Type]) ,[measure] )

     

    filter on Rank 
    Rank Top N= CALCULATE(if([Rank]<=SELECTEDVALUE(Slider_BP[Value]),[measure],BLANK()) ,VALUES(sheet1[Business_Process_Type]))

     

    using TOPN 

    Top N = CALCULATE([measure],TOPN(10,all(sheet1[Business_Process_Type]),[measure],DESC),VALUES(sheet1[Business_Process_Type]))

Anonymous's avatar
Anonymous
Not applicable
5 years ago
amitchandak , Here you go-
parameter = VAR X= SELECTEDVALUE(Slider_BP[Value])
RETURN
SWITCH(TRUE(),
X=1,COUNT(Sheet1[Business_Process_Type]),
RANKX(
ALLSELECTED(Sheet1[Business_Process_Type]),
CALCULATE(COUNT(Sheet1[Business_Process_Type]),Sheet1[Category]=1)
=>X
COUNT(Sheet1[Business_Process_Type])
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Assume you have measure on which you want rank 

     

    measure =CALCULATE(COUNT(Sheet1[Business_Process_Type]),filter(Sheet1,Sheet1[Category]=1))

     

    Rank you want 

     

    rank = rankx(allselected(Sheet1[Business_Process_Type]) ,[measure] )

     

    filter on Rank 
    Rank Top N= CALCULATE(if([Rank]<=SELECTEDVALUE(Slider_BP[Value]),[measure],BLANK()) ,VALUES(sheet1[Business_Process_Type]))

     

    using TOPN 

    Top N = CALCULATE([measure],TOPN(10,all(sheet1[Business_Process_Type]),[measure],DESC),VALUES(sheet1[Business_Process_Type]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak Can we write in a single dax?