Dax to compare 2 rows and return result base on condition

[earsuyheng]

Dear All,

 I need your help to write dax code for the following codition

if Trans. No is equal and category is equal to "Cash and cash equivalent" I want the result in Column Type as shown in the following table.

for example JE101 =JE101 and one of the JE101 is equal "Cash and cash equivalent " under column category.

Posting Date	Due Date	Series	Doc. No.	Trans. No.	Account	Type	Main Class	Category	Type
31-Jul-18	31-Jul-18	Primary	JE 101	101	1101161 - CASH IN BANK USD - CANADIA SAVING (MEF) - 1999	Asset	BS	Cash and cash equivalent
31-Jul-18	31-Jul-18	Primary	JE 101	101	8081200 - INTEREST INCOME WHT	Expense	PL	Non-Cash	Cash
31-Jul-18	31-Jul-18	Primary	JE 102	102	9501100 - INTEREST INCOME	Other Income	PL	Non-Cash	Cash
31-Jul-18	31-Jul-18	Primary	JE 102	102	1101162 - CASH IN BANK USD - CANADIA SAVING INCOME - 1169	Asset	BS	Cash and cash equivalent
31-Jul-18	31-Jul-18	Primary	JE 103	103	1101162 - CASH IN BANK USD - CANADIA SAVING INCOME - 1169	Asset	BS	Cash and cash equivalent
31-Jul-18	31-Jul-18	Primary	JE 103	103	8081200 - INTEREST INCOME WHT	Expense	PL	Non-Cash	Cash
16-Aug-18	16-Aug-18	Primary	DT 3	104	2212200 - VAT INPUT	Asset	BS	Non-Cash
16-Aug-18	16-Aug-18	Primary	DT 3	104	4001100 - ACCOUNT PAYABLE - SUPPLIERS	Liabilities	BS	Non-Cash
16-Aug-18	16-Aug-18	Primary	DT 3	104	2212400 - PREPAID EXPENSES	Asset	BS	Non-Cash
13-Aug-18	13-Aug-18	Primary	JE 105	105	4501600 - LOAN FROM BOD - OV4 THE CANAL	Liabilities	BS	Non-Cash	Cash
13-Aug-18	13-Aug-18	Primary	JE 105	105	1101163 - CASH IN BANK USD - CANADIA CURRENT INVEST - 1168	Asset	BS	Cash and cash equivalent
14-Aug-18	14-Aug-18	Primary	JE 106	106	4501600 - LOAN FROM BOD - OV4 THE CANAL	Liabilities	BS	Non-Cash	Cash
14-Aug-18	14-Aug-18	Primary	JE 106	106	1101163 - CASH IN BANK USD - CANADIA CURRENT INVEST - 1168	Asset	BS	Cash and cash equivalent

[PattemManohar · Accepted solution]

@earsuyheng Please try this as a New Column

Type1 = IF(CALCULATE(COUNTROWS(Test201Lkp),FILTER(ALL(Test201Lkp),Test201Lkp[DocNo]=EARLIER(Test201Lkp[DocNo]) && Test201Lkp[Category] = "Cash and cash equivalent"))>0 && Test201Lkp[Category] <> "Cash and cash equivalent","Cash")

[earsuyheng]

Dear Pattem,

Thanks for your solution.

Many thanks for your time and contribution.

Regards,

Heng