Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a record rank filter on three columns

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)
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.