Create a record rank filter on three columns

[MarkusEng1998]

I have a report, where the number of rows vary per the Discipline (e.g. Door Hardware (DFH), Plumbing, Electrical, etc.)

since I don't have PowerBI Premium, I am manually managing pagination.

I found a way to count the rows using a rank to filter on. So each page would be a based o a range of rank numbers.

However my table ItemsScheduleLegend has all Disciplines. I want to only create the sequential rank (e.g. 1-100) based on a filter of the Discipline, ItemRefSortRevised and DoorType (not included in the script)

Don't enumerate records not in the criteria.

Please advise

_RankDFH =

VAR __tableDFH = FILTER(ALL('ItemScheduleLegend'),[Discipline] > EARLIER('ItemScheduleLegend'[Discipline]) && 'ItemScheduleLegend'[Resp]="DFH")

VAR __tableSameDFH = FILTER(ALL('ItemScheduleLegend'),[Discipline] = EARLIER('ItemScheduleLegend'[Discipline]) && [ItemRefSortRevised]< EARLIER(ItemScheduleLegend[ItemRefSortRevised]))

Return

countrows(__tableDFH)+1 + Countrows(__tableSameDFH)

[MarkusEng1998 · Accepted solution]

I solved my pagination issue with a simpler approach. I created separate queries for each of the disciplines. After applying the desired filters, I added an index column. I can now filter which records display on each page. e.g. page 1 filter [index]<=40, page 2 40 > [Index} <= 80, etc.

Just have to figure out if there is a way to delete a report tab if the query has zero records.

[V-lianl-msft]

Thanks for your sharing.