Forum Discussion

annetoal's avatar
annetoal
Helper II
6 years ago

Filtering rows based on column contents

I am trying to make a report in PowerBI that shows the status of files in a list. The rows in the dataset are being pushed into the table from a connector in Flow. So I want to show a file as Pending only if it hasn't later been Approved or Rejected.

 

Each file has a uniqie ID. My idea was to filter the Pending row if there is an exact match in the FileID column. In a PowerBI report, how can I only display the row that says Pending when there is not a duplicate number in FileID for Approve or Reject?

 

In the example below, I only want to show the Approve row for upload-demo.docx, the Reject row for test-send.xlsx, and the Pending row for export.site.txt. Please help!

 

FileIDFilenameStatus
5MFIYUS2upload_demo.docxApprove
5MFIYUS2upload_demo.docxPending
M3R33DJLtest-send.xlsxPending
M3R33DJLtest-send.xlsxReject
CYFH25YPexport-site.txtPending
Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    You are going to need a flag column or measure along the lines of:

     

    Flag Column =
  VAR __Statuses = SELECTCOLUMNS(FILTER('Table',[FileID] = EARLIER([FileID]),[Status])
RETURN
  SWITCH(TRUE(),
    [Status] = "Approvied" || [Status] = "Rejected",1,
    NOT("Approved" IN __Statuses) && NOT("Rejected" IN __Statuses),1,
    0
  )
    • annetoal's avatar
      annetoal
      Helper II
      6 years ago

      Thank you for the code. Since this is a report coming from a streaming dataset, won't I be limited to just the one table 'RealTimeData'? PowerBI won't allow the table Statuses in the report with the table made from the streaming dataset. Apologies for the noob questions.

       

      Anne