Dynamic Top N

[Anonymous]

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 selection -

Based on below chart I want to give top n selection.

In the place of Axisi kept Business process type and in the place of value I kept parameter, the measure i have created i.e. 

parameter = VAR X= SELECTEDVALUE(Slider_BP[Value])

VAR Y= CALCULATE(COUNT(Sheet1[Business_Process_Type]),Sheet1[Category]=1)

RETURN

Y

I know i am missingout something in my dax that y the slicer is not working.

Can anyone help me with this?

Thank you in advance!

[Anonymous]

@amitchandak , tried but did not work.

[amitchandak]

@Anonymous , change this statement and try

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

if not share your TOPN code

[Anonymous]

@amitchandak, I tried to write below formula as per the post that you referred. Could you please rectify my dax?

[amitchandak]

@Anonymous , I think the Rank is not used correctly.

Can you paste the formula in text format

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[Anonymous]

@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 · Accepted solution]

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

@amitchandak Can we write in a single dax?

[amitchandak]

@Anonymous , refer to this blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415