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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Top N

I have the following view in Power BI:

 

Top N 1.PNG

The above visual contains TOP 10 job titles by the count of PERNR. This is a visual level filter on the table as follows: 

Top N 2.PNG

But as shown in the pic, there are 2 counts that are 34, and TOP 10 accounts for both. But overall now there are 11 shown. Is there any way that I can show only the Top 10? 

 

I do not want the 11th one though they are both 34.  Thank You!              

Top N 3.png

1 ACCEPTED SOLUTION

@Anonymous ,

 

Sorry for the mistake I have made, please change the formula with dax below:

Row Number =
COUNTROWS (
    FILTER (
        IHR,
        IHR[Column Count of Assignee] <= EARLIER ( IHR[Column Count of Assignee] )
            && IHR[Index] <= EARLIER ( IHR[Index] )
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
theov
Advocate II
Advocate II

Hi there, if you want to make a dynamic Top N in slicer check this out 🙂
https://www.youtube.com/watch?v=A2K-leEcgY8

v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

The built-in visual level filter doesn't have such feature. Suppose [Count of PERNR] is a calculate column(if it's a measure, please convert it to calculate column), you need to create an additional index and then create a calculate column using DAX below:

Row Number =
COUNTROWS (
    FILTER (
        Table,
        Table[Count of PERNR] <= EARLIER ( Employee[Count of PERNR] )
            && Table[Index] >= EARLIER ( Table[Index] )
    )
)
Then use TOPN filter on the [Row Number] column you will achieve what you want.
 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@v-yuta-msft 

Hey, what exactly is Table[Index].. From where does the index field come?

 

@Anonymous ,

 

You need to build an index column in your table so that you can compare the sequence between rows. Click query editor-> add columns-> index.

 

Regards,

Jimmy Tao

Anonymous
Not applicable

@v-yuta-msft  Hey, I tried that..

My calculated column is as follows:

Top10_2.PNG

 

The result i get is as follows:

Top10_3.PNG

But a normal table of job titles and count of pernr sorted in descending order shows the top 10 as follows

   

Top 10.png  

 

As shows, the 2 highlighted columns are eliminated in the 2nd visual... Not sure where I am going wrong.. 

@Anonymous ,

 

Sorry for the mistake I have made, please change the formula with dax below:

Row Number =
COUNTROWS (
    FILTER (
        IHR,
        IHR[Column Count of Assignee] <= EARLIER ( IHR[Column Count of Assignee] )
            && IHR[Index] <= EARLIER ( IHR[Index] )
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.