Top N

[Anonymous]

I have the following view in Power BI:

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

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!              

[theov]

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]

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

@v-yuta-msft 

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

[v-yuta-msft]

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

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

My calculated column is as follows:

The result i get is as follows:

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

  

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

[v-yuta-msft · 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.