topic Re: Compare data in same table based on column value in DAX Commands and Tips

Compare data in same table based on column value

char23 — Tue, 23 Jul 2024 12:47:39 GMT

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

Re: Compare data in same table based on column value

Greg_Deckler — Tue, 23 Jul 2024 12:54:06 GMT

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

Re: Compare data in same table based on column value

char23 — Tue, 23 Jul 2024 13:42:21 GMT

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

Re: Compare data in same table based on column value

Greg_Deckler — Tue, 23 Jul 2024 13:44:22 GMT

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

Re: Compare data in same table based on column value

char23 — Tue, 23 Jul 2024 14:45:25 GMT

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

Re: Compare data in same table based on column value

Greg_Deckler — Tue, 23 Jul 2024 16:23:04 GMT

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