Forum Discussion

char23's avatar
char23
Icon for Helper II rankHelper II
2 years ago
Solved

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
"Calculated Column"
"DAX Commands and Tips"
"DAX help"
  • aduguid's avatar
    aduguid
    2 years ago

    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)

1 Reply

  • aduguid's avatar
    aduguid
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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)