- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi @char23 ,
try like:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi @char23 ,
try like:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
ID | ENTRY | Deleted IDs |
A | 3 | |
B | 3 | |
C | 3 | |
D | 3 | |
E | 3 | |
A | 2 | |
B | 2 | |
C | 2 | |
D | 2 | |
A | 1 | |
B | 1 | |
D | 1 | |
G | 1 | Deleted |
H | 1 | Deleted |

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
03-24-2024 09:14 PM | |||
02-14-2022 10:19 AM | |||
07-18-2024 01:53 AM | |||
07-17-2024 05:45 AM | |||
01-02-2024 11:33 AM |
User | Count |
---|---|
23 | |
12 | |
10 | |
9 | |
8 |
User | Count |
---|---|
16 | |
15 | |
15 | |
12 | |
10 |