Forum Discussion

char23's avatar
char23
Helper II
2 years ago
Solved

Compare data in same table based on column value

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 
"Calculated Column"
"DAX help"
"Need Help"
DAX Command
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    char23 Yes, I would modify it as follows:

    Deleted Previous Tasks =

VAR _id = 'Table'[Id]
VAR _entry = 'Table'[Entry]
VAR _minEntry = MINX( ALL( 'Table' ), [Entry] )
VAR _table = SELECTCOLUMNS(FILTER('Table', 'Table'[Entry] = _entry - 1), "_id", 'Table'[Id])
VAR _result =
    SWITCH( TRUE(),
    _entry > 2 || _entry = _minEntry, BLANK(),
    _id IN _table, BLANK(),
    "Deleted"
    )
RETURN
_result

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    char23 Try:

    Deleted IDs (column) = 
  VAR __ID = [ID]
  VAR __Entry = [Entry]
  VAR __Table = SELECTCOLUMNS( FILTER( 'Table', [Entry] = __Entry - 1 ), "__ID", [ID] )
  VAR __Result =
    SWITCH( TRUE(),
      __Entry = 3, BLANK(),
      __Entry IN __Table, BLANK(),
      "Deleted"
    )
RETURN
  __Result
    • char23's avatar
      char23
      Helper II
      2 years ago

      Thank you for the help. I tried this method, and I am now getting an error. "Function 'CONTAINSROW' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values." This is what I have below:

       

      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 IN _table, BLANK(),
          "Deleted"
          )
      RETURN
      _result
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        char23 Sorry, messed that up slightly:

        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(),
    _id IN _table, BLANK(),
    "Deleted"
    )
RETURN
_result