topic Re: LOOKUP in SAME TABLE in DAX Commands and Tips

LOOKUP in SAME TABLE

Anonymous — Mon, 20 Sep 2021 23:50:09 GMT

Hi All,

I would like to ask assistance in the scenario below:

currently have a table with the first three columns, and I want to add a column for the Transaction Group.
Rules:

> If Item group is MB, get the first item group with same transaction no.
> If item is not MB, get row item group.

Transaction No	Item	Item Group	Transaction Group
1	1001	AN	AN
1	1002	AN	AN
1	1015	MB	AN
2	1004	RR	RR
2	1015	MB	RR
3	1001	AN	AN
3	1002	AN	AN
3	1015	MB	AN
3	1020	RR	RR

Thank you.

Re: LOOKUP in SAME TABLE

Greg_Deckler — Mon, 20 Sep 2021 23:56:34 GMT

Anonymous Maybe:

Transaction Group = IF( "Item Group" = "MB", VAR __First = MINX(FILTER('Table',[Transaction]=EARLIER([Transaction])),[Item]) RETURN MAXX(FILTER('Table',[Transaction]=EARLIER([Transaction]) && [Item]=__First),[Item Group]), [Item Group] )

Re: LOOKUP in SAME TABLE

edhans — Mon, 20 Sep 2021 23:56:50 GMT

Is this what you want?

You said you wanted the "row item group" if Item Group wasn't MB, but I wasn't sure what that meant. I pulled the item group from the same row.

Code here:

Column = VAR varItemGroup = 'Table'[Item Group] VAR varTransaction = 'Table'[Transaction No] RETURN IF( varItemGroup = "MB", MINX( FILTER( 'Table', 'Table'[Transaction No] = varTransaction ), 'Table'[Transaction Group] ), 'Table'[Item Group] )

Note: This is a calculated column formula and must be entered as a New Column to work.

Re: LOOKUP in SAME TABLE

Anonymous — Tue, 21 Sep 2021 00:05:51 GMT

Hi. noticed that you used the transaction group column in the formula. What i meant was that, I am trying to have the trans group column be the output.

Thank you.

Re: LOOKUP in SAME TABLE

Greg_Deckler — Tue, 21 Sep 2021 00:15:17 GMT

Anonymous I think I got it correct, please check, I did not use Transaction Group in my formula.

Re: LOOKUP in SAME TABLE

Anonymous — Tue, 21 Sep 2021 00:36:26 GMT

Hi Greg, this worked for my sample dataset. will check on the full dataset before accepting as solution. thank you.

Re: LOOKUP in SAME TABLE

edhans — Tue, 21 Sep 2021 00:46:29 GMT

Ahh.. DIdn't understand TransactionGroup was the expected result. Try this Anonymous

Column = VAR varItemGroup = 'Table'[Item Group] VAR varItem = 'Table'[Item] VAR varTransaction = 'Table'[Transaction No] RETURN IF( varItemGroup = "MB", MINX( FILTER( 'Table', 'Table'[Transaction No] = varTransaction && 'Table'[Item] < varItem ), 'Table'[Item Group] ), 'Table'[Item Group] )