Filtering rows based on a lookup match

[annetoal]

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

[camargos88]

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 visual

Ricardo

[annetoal]

I'm sorry, but I don't speak Spanish enough to understand your answer.

Anne

[sanimesa]

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. 

[amitchandak]

@annetoal 

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])