Forum Discussion

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

Compare and lookup date in same table

Hello, I am trying to add a calculated column to list "deleted" for any ID that does not show in the most recent entry (1). What is the best way to approach this? I tried creating with using if state...
"DAX Commands and Tips"
"DAX help"
Dax commands help
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi char23 ,

     

    try like:

    Column =
    VAR _entry = [entry]
    VAR _id = [id]
    VAR _entrypre =_entry - 1
    VAR _idlistpre = CALCULATETABLE(VALUES(data[id]), data[entry] = _entrypre, ALL())
    VAR _result = IF( _id in _idlistpre || ISEMPTY(_idlistpre) || _entry>2 , BLANK(), "Deleted")
    RETURN  _result
     

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
2 years ago

Hi char23 ，

 

try to add a column like:

Column = 
VAR _entry = [entry]
VAR _id = [id]
VAR _entrypre =_entry - 1
VAR _idlistpre = CALCULATETABLE(VALUES(data[id]), data[entry] = _entrypre, ALL())
VAR _result = IF( _id in _idlistpre || ISEMPTY(_idlistpre), BLANK(), "Deleted")
RETURN  _result

 

it worked like:

 

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

    What if I want to ignore any value in entry column greater than 2. How would I modify? 

    IDENTRYDeleted IDs
    A3 
    B3 
    C3 
    D3 
    A2 
    B2 
    C2Deleted
    D2 
    A1 
    B1 
    D1 
    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      2 years ago

      hi char23 ,

       

      try like:

      Column =
      VAR _entry = [entry]
      VAR _id = [id]
      VAR _entrypre =_entry - 1
      VAR _idlistpre = CALCULATETABLE(VALUES(data[id]), data[entry] = _entrypre, ALL())
      VAR _result = IF( _id in _idlistpre || ISEMPTY(_idlistpre) || _entry>2 , BLANK(), "Deleted")
      RETURN  _result