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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
char23
Helper II
Helper II

Searching for deleted rows in the same table

Hello, I am trying to mark any item that is not found earlier in the table as "Deleted " , but I want to ignore any data with value 3 or greater in my [entry] column. I am trying to add the [Deleted IDs] column below in green, but only want to label "deleted" to those with entry 2  if not found in group with entry 1. Thank you for any help on this!

 

IDENTRYDeleted IDs
A3 
B3 
C3 
D3 
E3 
A2 
B2 
C2Deleted
D2 
A1 
B1 
D1 

 

This is what I have so far, but I am only getting blank returned in the entire column. But in my table, I definetly have rows present with entry 2, but deleted from entry 1. 

 

Deleted Previous Tasks =

VAR _id = 'Table'[Id]
VAR _entry = 'Table'[Entry]
VAR _table = SELECTCOLUMNS(FILTER('Table', 'Table'[Entry] = _entry - 1), "_id", 'Table'[Id])
VAR _result =
    SWITCH( TRUE(),
    _entry > 2, BLANK(),
    _entry = 1, BLANK(),
    _id IN _table, BLANK(),
    "Deleted"
    )
RETURN
_result
1 ACCEPTED SOLUTION
aduguid
Super User
Super User

Try this measure 

Deleted IDs = 
VAR _currentID = 'Table'[ID]
VAR _currentEntry = 'Table'[ENTRY]
VAR _isDeleted =
    IF(
        _currentEntry = 2 &&
        NOT (
            CALCULATE(
                COUNTROWS('Table'),
                FILTER(
                    'Table',
                    'Table'[ID] = _currentID &&
                    'Table'[ENTRY] = 1
                )
            ) > 0
        ),
        "Deleted",
        BLANK()
    )
RETURN
    IF(_currentEntry >= 3, BLANK(), _isDeleted)

View solution in original post

1 REPLY 1
aduguid
Super User
Super User

Try this measure 

Deleted IDs = 
VAR _currentID = 'Table'[ID]
VAR _currentEntry = 'Table'[ENTRY]
VAR _isDeleted =
    IF(
        _currentEntry = 2 &&
        NOT (
            CALCULATE(
                COUNTROWS('Table'),
                FILTER(
                    'Table',
                    'Table'[ID] = _currentID &&
                    'Table'[ENTRY] = 1
                )
            ) > 0
        ),
        "Deleted",
        BLANK()
    )
RETURN
    IF(_currentEntry >= 3, BLANK(), _isDeleted)

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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