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?