Forum Discussion
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 statements, but could not get it to work. I am trying to add the column in green.
| ID | ENTRY | Deleted IDs |
| A | 2 | |
| B | 2 | |
| C | 2 | Deleted |
| D | 2 | |
| A | 1 | |
| B | 1 | |
| D | 1 |
7 Replies
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 _resultit worked like:
What if I want to ignore any value in entry column greater than 2. How would I modify?
ID ENTRY Deleted IDs A 3 B 3 C 3 D 3 A 2 B 2 C 2 Deleted D 2 A 1 B 1 D 1
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.
What if I have more than two values in my entry column and want to ingnore anything greater than 2. How would I modify?
ID ENTRY Deleted IDs A 3 B 3 C 3 D 3 A 2 B 2 C 2 Deleted D 2 A 1 B 1 D 1 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.
