Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

Reply
annetoal
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.

Sincerely,

Anne

4 REPLIES 4
camargos88
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
 
Ricardo


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.

Anne

sanimesa
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. 

amitchandak
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.