How to create a row number in a table that allows filtering

[Former Member]

Hi,

I would like to share a way to filter a table by the line numbers of its rows.

Firstly, there are some restrictions on it´s use :

1 - The line numbers in this solution is based on the default ordering and the position of each object in the table elements;

2 - If you re-order or re-arrange the table , you´ll have to alter the formula.

That´s said, , let´s start!

I´ll use a query based on e-fashion that returns [Year], [Month] and [Quantity sold] , which gives me the following table :

I´ll need three variables :

[row], a measure variable . = 1 +( Previous(Self) In ([Year];[Month]), this will give me the number of each line. The secret here is the use of the In

context redefinition. Through the dimensions in the In operator one can set how the rows will be counted. In this case, ordered by [Year] and then by month. If the order is altered ([Month;[Year]) the numbering of the rows will be as the table was sorted by [Month] and then [Year].

The following two variables will be dimension variables, [ln] and [Linenumber].

The first one , [ln] = [row], will hold the value of the lines as a dimension.

The second, [RowNumber]  = NoFilter([ln] ForEach ([Year];[Month];[ln])) ), will be responsible for unically identify each row in the table. The process of unically associating a row number to each row is achieved through the use of the ForEach operator.

Redefining the context through ForEach, in this case means that [RowNumber] can be used outside the block as a standalone dimension

The redefinition of the context must follow the order of the dimension  objects inside the table , if the table is re-arranged, the contexto redefinition in the formula must be altered to consider it.

The report should be filtered by [LineNuber]

The next Picture, shows the table filtered by [LineNumber] between 5 and 10

This process, besides being used to filtering has many other uses , for instance creating a dense ranking at report side.

Cheers,

Rogerio