Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
char23
Helper I
Helper I

Compare data in same table based on column value

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!

 

IDENTRYDeleted IDs
A3 
B3 
C3 
D3 
E3 
A2 
B2 
C2Deleted
D2 
A1 
B1 
D1 
1 ACCEPTED SOLUTION

@char23 Yes, I would modify it as follows:

Deleted Previous Tasks =

VAR _id = 'Table'[Id]
VAR _entry = 'Table'[Entry]
VAR _minEntry = MINX( ALL( 'Table' ), [Entry] )
VAR _table = SELECTCOLUMNS(FILTER('Table', 'Table'[Entry] = _entry - 1), "_id", 'Table'[Id])
VAR _result =
    SWITCH( TRUE(),
    _entry > 2 || _entry = _minEntry, BLANK(),
    _id IN _table, BLANK(),
    "Deleted"
    )
RETURN
_result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@char23 Try:

Deleted IDs (column) = 
  VAR __ID = [ID]
  VAR __Entry = [Entry]
  VAR __Table = SELECTCOLUMNS( FILTER( 'Table', [Entry] = __Entry - 1 ), "__ID", [ID] )
  VAR __Result =
    SWITCH( TRUE(),
      __Entry = 3, BLANK(),
      __Entry IN __Table, BLANK(),
      "Deleted"
    )
RETURN
  __Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thank you for the help. I tried this method, and I am now getting an error. "Function 'CONTAINSROW' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values." This is what I have below:

 

VAR _id = 'Table'[Id]
VAR _entry = 'Table'[Entry]
VAR _table = SELECTCOLUMNS(FILTER('Table', 'Table'[Entry] = _entry - 1), "_id", 'Table'[Id])
VAR _result =
    SWITCH( TRUE(),
    _entry > 2, BLANK(),
    _entry IN _table, BLANK(),
    "Deleted"
    )
RETURN
_result

@char23 Sorry, messed that up slightly:

VAR _id = 'Table'[Id]
VAR _entry = 'Table'[Entry]
VAR _table = SELECTCOLUMNS(FILTER('Table', 'Table'[Entry] = _entry - 1), "_id", 'Table'[Id])
VAR _result =
    SWITCH( TRUE(),
    _entry > 2, BLANK(),
    _id IN _table, BLANK(),
    "Deleted"
    )
RETURN
_result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thank you! This almost does what I want it to do. But now, it just lists "Deleted" for all rows with entry 1. I want to label those with entry 2 as deleted if not found in one. Could it be something with the _result variable? Should I change one of the conditions? Below is what I have now. 

 

Deleted Previous Tasks =

VAR _id = 'Table'[Id]
VAR _entry = 'Table'[Entry]
VAR _table = SELECTCOLUMNS(FILTER('Table', 'Table'[Entry] = _entry - 1), "_id", 'Table'[Id])
VAR _result =
    SWITCH( TRUE(),
    _entry > 2, BLANK(),
    _id IN _table, BLANK(),
    "Deleted"
    )
RETURN
_result

@char23 Yes, I would modify it as follows:

Deleted Previous Tasks =

VAR _id = 'Table'[Id]
VAR _entry = 'Table'[Entry]
VAR _minEntry = MINX( ALL( 'Table' ), [Entry] )
VAR _table = SELECTCOLUMNS(FILTER('Table', 'Table'[Entry] = _entry - 1), "_id", 'Table'[Id])
VAR _result =
    SWITCH( TRUE(),
    _entry > 2 || _entry = _minEntry, BLANK(),
    _id IN _table, BLANK(),
    "Deleted"
    )
RETURN
_result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors