Lookupvalue

[gauravnarchal]

Hello

I need your help on the below

For each IDNumber, return the transaction type 1 or 3 only

Data

IDNumber	TransactionType
912021	1
912021	3
912533	3
912533	1
898801	1
898801	3
899785	3
899785	1
900140	1
900140	3
901509	3
901509	1
900140	1
900140	3
900140	4
917821	1
917821	3
917821	5

Result

IDNumber	TransactionType	Result
912021	1	1
912021	1	1
898801	1	1
898801	1	1
900140	1	3
900140	3	3
901509	3	3
901509	1	3
900140	1	3
900140	3	3
900140	4	3
917821	1	3
917821	3	3
917821	5	3

[Anonymous · Accepted solution]

Hi @gauravnarchal ,

Refer the formula.

Column = CALCULATE(MAX(data[TransactionType]),FILTER(ALLEXCEPT(data,data[IDNumber]),data[TransactionType]=1||data[TransactionType]=3))

 

Best Regards,

Jay

[mwegener]

Hi @gauravnarchal ,

I do not understand the requirement.
Why is the IDNumber 912533 not displayed in the result?
Why is the IDNumber 912021, 898801, ... only the 1 is displayed?
Why is the IDNumber 900140 displayed only the 3?