The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
ID | ENTRY | Deleted IDs |
A | 3 | |
B | 3 | |
C | 3 | |
D | 3 | |
E | 3 | |
A | 2 | |
B | 2 | |
C | 2 | Deleted |
D | 2 | |
A | 1 | |
B | 1 | |
D | 1 |
This is what I have so far, but I am only getting blank returned in the entire column. But in my table, I definetly have rows present with entry 2, but deleted from entry 1.
Solved! Go to Solution.
Try this measure
Deleted IDs =
VAR _currentID = 'Table'[ID]
VAR _currentEntry = 'Table'[ENTRY]
VAR _isDeleted =
IF(
_currentEntry = 2 &&
NOT (
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[ID] = _currentID &&
'Table'[ENTRY] = 1
)
) > 0
),
"Deleted",
BLANK()
)
RETURN
IF(_currentEntry >= 3, BLANK(), _isDeleted)
Try this measure
Deleted IDs =
VAR _currentID = 'Table'[ID]
VAR _currentEntry = 'Table'[ENTRY]
VAR _isDeleted =
IF(
_currentEntry = 2 &&
NOT (
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[ID] = _currentID &&
'Table'[ENTRY] = 1
)
) > 0
),
"Deleted",
BLANK()
)
RETURN
IF(_currentEntry >= 3, BLANK(), _isDeleted)
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
20 | |
15 | |
14 | |
10 | |
7 |