Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RashmitaR
Helper IV
Helper IV

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
RaulSanchez
Frequent Visitor

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
RaulSanchez
Frequent Visitor

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
Resident Rockstar
Resident Rockstar

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 

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

Without Dimension how can u show the top value ?

 

 

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

Its cool...

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

 

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

DAN
Advocate II
Advocate II

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
Community Champion
Community Champion

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 Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.