DAX query to obtain top n columns

[RashmitaR]

Hi

I wanted to obtain the top 10 s_country names on the basis of thr Headcount(Headcount = distinctcount(N_RR_ID)) could someone please suggest me a DAX query for the same.Above is a pic of my datasets and the columns I am using.

I tried using

R_Rank = RANKX(ALL(D_FIN_CUSTOMER), SUM(RELATEDTABLE(F_RMG_TIMEANDBILLING), [Headcount]))

Unable to get desired output.

[RaulSanchez · Accepted solution]

Hey,

make it simple, 

 Rankx Measure:

 RANKX(ALL(F_RR_DETAIL[S_COUNTRY]);[Headcount])

after that 

- select the table visualization and add  the RANKX measure, the  headcount measure  and the S_COUNTRY to the Values.

- Add an advance filter to the visualisation(less than or equal to 10) using the RANKX Measure. 

Hope it work's

[Baskar]

Hi Rashmita

Step :1

   Create a Measure :

                     Measure 1 = Sum(Headcount)

Step :2

Create Rank Measure

                    Rank = RankX(Allselected(s_country),Measure 1,,Desc,Dense)

Step :3

Place the Rank function in Visual Filter and Choose the advanced filter less than , and enter 11,

Result : u will get the Top 10 Value based on Country.

Try, and Let me know 

[RashmitaR]

Hi @Bhaskar Thanks for your reply but i wanted the ranks according to the headcount per customer rather than country wise.

[Baskar]

Without Dimension how can u show the top value ?

okay anyhow , just simply replace the Country with your tablename thats it... 

Its cool...

[RashmitaR]

@Bhaskar As suggested earlier i have tried your solution. just for clarification I have a table with N_RR_Id and cust_name columns where N_RR_ID is the id number of the employee and cust_name = customer name . I tried headcount = DISTINCTCOUNT(F_RR_DETAIL[N_RR_ID]) Measure 1 = DISTINCTCOUNT([N_RR_ID]) Rank = Rankx(Allselected(f_RR_DETAIL)[,Measure 1],,Desc,Dense) I am getting rank as 1 for all the cust_name.

[Baskar]

Can u please give your data sample in short as image format i will clarify your doubt or solve your prob....

[DAN]

Hi RashmitaR,

1/ You can follow this post and adapte the DAX formula to your data "Limit Top N Elements in a Ranking" - it worked perfectly for me

http://www.daxpatterns.com/parameter-table/

1 2 3 4 5 6 7 8 9 10 11 12

Top10SalesAmount := IF (     HASONEVALUE ( Sales[Product] ),     IF (         RANKX (             ALL ( Sales[Product] ),             [SalesAmount]         ) <= 10,         [SalesAmount],         BLANK ()     ) )

2/ Or, you can wait for the new functionnality which will be available soon :

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6515731-top-n-filters

[kcantor]

You can also apply RankX to the data and then use the page level filter to select how many items you would like to display. I rank our entire product line and then filter the page for top 10. That way, if someone wants the top 20 or top 50, I don't have to make any changes except to the filter.