cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mattmcdowell_ Regular Visitor
Regular Visitor

Limiting number of rows in visual

Hi all,

 

In the below visual report I have many rows of data. What I would like to achieve is to show only 15 rows, sorted by the due date column.  In the screenshot below I applied TOP N bottom filtering on the due date field, using the due date as data, with the Bottom N value of 4 as an example. This gets me September 12, September 17th, September 28th, and then 4 rows that show the same date of October 3rd.  What I would want to expect is to see only the first October 3rd row.

 

How can I achieve what I'm after?

 

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Mattmcdowell_ Regular Visitor
Regular Visitor

Re: Limiting number of rows in visual

I'm not too versed in DAX just yet but I did find a solution. What I did was in my query sort the query by date, ascending. Then I used the index column to create a column with values starting from 0. Then on my visual I can use the index as a report level filter to achieve what I wanted.

 

Thanks for looking into it for me!

View solution in original post

6 REPLIES 6
paranoodle Regular Visitor
Regular Visitor

Re: Limiting number of rows in visual

What is your criteria for deciding which is the only row you'd like to see for a given date?

 

If, for example, you want to take the first Production Order No, does it achieve the desired result if you set its aggregation to "First" instead of "Don't Summarize"? You might also have to change the aggregate for the other columns if they're also set to "Don't Summarize".

Mattmcdowell_ Regular Visitor
Regular Visitor

Re: Limiting number of rows in visual

Sorry I think I did a bad job of explaining this.  I want to see 15 rows of data, from the oldest date onwards.  It's ok to have multiple of the same dates.  Using the Top N function is the closest I've got to achieving this result.  But because the dates are the same for October 3rd as an example - it includes that as ONE of the FOUR top N criteria that is being displayed. 

 

 

ryan_mayu Established Member
Established Member

Re: Limiting number of rows in visual

@Mattmcdowell_

 

In the table, you can only set one top N. You see 4 3rd Oct data because they are all the forth smallest date.

 

Have you tried to create another table? You can set two ranking creterias. Hope this is helpful. Thanks.

 

Table = TOPN(4,Table1,'Table1'[date],ASC,Table1[amount],DESC)


c1.JPGc2.JPG
paranoodle Regular Visitor
Regular Visitor

Re: Limiting number of rows in visual

Aaah okay yeah, I completely misunderstood your problem then. I'm not entirely sure what would be the best solution in this case. If you have access to using DAX to select your lines, you should be able to manuall do a TOPN to eliminate ties

 

Something along the lines of:

Top15Dates = TOPN(15, my_table, my_table[Due Date], ASC, my_table[Production Order No], ASC)

 Does this work as expected?

Mattmcdowell_ Regular Visitor
Regular Visitor

Re: Limiting number of rows in visual

I'm not too versed in DAX just yet but I did find a solution. What I did was in my query sort the query by date, ascending. Then I used the index column to create a column with values starting from 0. Then on my visual I can use the index as a report level filter to achieve what I wanted.

 

Thanks for looking into it for me!

View solution in original post

Microsoft v-cherch-msft
Microsoft

Re: Limiting number of rows in visual

Hi @Mattmcdowell_

 

Glad to hear you've solved it, please accept your solution to close this thread, that way, other community members will easily find the solution when they get same issue.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors