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
     

     

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

hello char23 

 

please check if this accomodate your need.

Deleted IDs =
var _ID = 'Table'[ID]
var _Duplicate = COUNTROWS(FILTER('Table','Table'[ID]=_ID))
Return
IF(
    _Duplicate=1,
    "Deleted",
    ""
)

 

The idea is you want to look for duplicate. If no duplicate (count value is 1), then you will have string "Deleted".

 

Hope this will help you.

Thank you.

 

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

    What if I have more than two values in my entry column and want to ingnore anything greater than 2. How would I modify? 

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

      hello char23 

       

      modify the conditional if statement should do.

      Deleted IDs =
      var _ID = 'Table 2'[ID]
      var _Duplicate = COUNTROWS(FILTER('Table','Table'[ID]=_ID))
      Return
      IF(
          _Duplicate=1&&'Table 2'[ENTRY]<=2,
          "Deleted",
          ""
      )

       

      Adding 'ENTRY' less than or equal 2 in if statement.


      Hope this will help you.

      Thank you.

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

        Do you use two different tables in this solution? I tried this method, but I am getting deleted by all new IDs where ENTRY = 1. But I want ID C where ENTRY = 2 to show deleted because ID C is not found in the group where ID=1. 

         

        IDENTRYDeleted IDs
        A3 
        B3 
        C3 
        D3 
        E3 
        A2 
        B2 
        C2 
        D2 
        A1 
        B1 
        D1 
        G1Deleted
        H1Deleted