cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RashmitaR Member
Member

DAX query to obtain top n columns

rank_image.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RaulSanchez Frequent Visitor
Frequent Visitor

Re: DAX query to obtain top n columns

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

 

 

 

 

 

View solution in original post

8 REPLIES 8
DAN Regular Visitor
Regular Visitor

Re: DAX query to obtain top n columns

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 Super Contributor
Super Contributor

Re: DAX query to obtain top n columns

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Baskar Super Contributor
Super Contributor

Re: DAX query to obtain top n columns

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

Re: DAX query to obtain top n columns

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

Re: DAX query to obtain top n columns

Without Dimension how can u show the top value ?

 

 

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

Its cool...

RashmitaR Member
Member

Re: DAX query to obtain top n columns

@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 Super Contributor
Super Contributor

Re: DAX query to obtain top n columns

 

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

RaulSanchez Frequent Visitor
Frequent Visitor

Re: DAX query to obtain top n columns

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

 

 

 

 

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors