Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II
Helper II

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.



Community Champion
Community Champion

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

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


Post Prodigy
Post Prodigy

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. 

Super User
Super User


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

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors