Keeping a constant number of returned rows

In today’s question we will be answering how we can keep the returned results limited to a defined/definable number of rows. That means for example if we say we want only 200 rows to be returned but we have four different segments then each segment should only hold 50 results. If we increase to 300 rows, each segment will be showing 75 results and if we filter out one segment, the remaining three would be able to show up to 100 rows per segment.

Getting into it

So how do we do this? First, create your initial table viz by simply putting Region and Customer Name from the Sample Superstore onto rows.

Next, create a Parameter that enables us to define the number of rows we want to see overall. Set to Integer and define as you please.

The magic sauce – a mixed Table Calculation / Level of Detail calculation filter

First off, don’t get me wrong here: you cannot mix table calculations and level of detail calculations by means of incorporating a table calc into (!) the LoD. You can, however, compare a table calc to the results of an LoD which is what we will be doing

//Filter
//maximum allowed number of rows is defined by parameter

INDEX() <= sum([p.MaxMarks] / {fixed: countd([Region])})

So basically, all we do is compare if the index of the row is less or equal to our maximum allowed rows / marks (marks is imprecise if you have more than one measure returned, so let’s stick with rows).

Put this on the filter shelf and edit table calculation. Set it to

So for every Region, compute the index by Customer Name. This will restart the index at every Region and compute per Customer Name.

The final step

Now all you have to do is to filter to “true” and set the Region to be in the context. Why that? Because otherwise, the LOD will calculate prior to the dimension filter and therefore ignore any filtering we might do.

And that is it. Feel free to recreate and play around. Maybe it will come in handy one day.

As always, appreciate your feedback.

Until next time

Steffen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s