cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Top N

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!

1 ACCEPTED SOLUTION
Community Support

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

6 REPLIES 6

Hi there, if you want to make a dynamic Top N in slicer check this out 🙂

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

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

Community Support

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

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

Community Support

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors