Forum Discussion
Filtering rows based on a lookup match
Hello everyone,
Here's my table 'Approvals':
| Index | Submitter | Submitted_datetime | Status | Approved_datetime | FileID |
| 1 | Anne <removed by Admin> | 4/1/2020 13:00 | Pending | A1009 | |
| 2 | Anne <removed by Admin> | 4/9/2020 10:07 | Pending | A1001 | |
| 3 | Anne <removed by Admin> | 4/9/2020 10:07 | Approved | 4/10/2020 12:01 | A1001 |
In my PowerBI report, I only want to display the Pending row if there IS NOT a matching FileID.
If there is a matching FileID, I want to only show the LATEST row with that FileID.
The rows that should display in the report are Index 1 and 3.
All assistance gratefully received.
Sincerely,
Anne
4 Replies
Hi annetoal ,
Create a calculated column with this code:
LastItem = IF('Table'[Index] = CALCULATE(MAX('Table'[Index]); ALLEXCEPT('Table'; 'Table'[FileID])); 1; 0)And filter the visualRicardoI'm sorry, but I don't speak Spanish enough to understand your answer.
Anne
you can create a column like this
countx(filter(table,[Submitter]=earlier(Submitter) && [FileID] = earlier(FileID) && [Status] ="Approved"),[FileID])
You need to get all blank row for this column
Or try a measure like
countX(filter(summarize(table,table[FileID], "_1",count(table[FileID]),"_2", countx(filter( [Status] ="Approved"),table[FileID])+0),[_2]>0),[FileID])
Can there be multiple approvals? If not then you can simply show all rows where FileID is blank or status is approved.
Perhaps if you post a bit more sample data, it would help understanding the use case.
