Limiting number of rows in visual

[Mattmcdowell_]

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?

[ryan_mayu]

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

[Anonymous]

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

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. 

[Anonymous]

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_ · Accepted solution]

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!

[v-cherch-msft]

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