cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
earsuyheng Frequent Visitor
Frequent Visitor

Dax to compare 2 rows and return result base on condition

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 DateDue DateSeriesDoc. No.Trans. No.AccountTypeMain ClassCategoryType
31-Jul-1831-Jul-18PrimaryJE 1011011101161 - CASH IN BANK USD - CANADIA SAVING (MEF) - 1999AssetBSCash and cash equivalent 
31-Jul-1831-Jul-18PrimaryJE 1011018081200 - INTEREST INCOME WHTExpensePLNon-CashCash
31-Jul-1831-Jul-18PrimaryJE 1021029501100 - INTEREST INCOMEOther IncomePLNon-CashCash
31-Jul-1831-Jul-18PrimaryJE 1021021101162 - CASH IN BANK USD - CANADIA SAVING INCOME - 1169AssetBSCash and cash equivalent 
31-Jul-1831-Jul-18PrimaryJE 1031031101162 - CASH IN BANK USD - CANADIA SAVING INCOME - 1169AssetBSCash and cash equivalent 
31-Jul-1831-Jul-18PrimaryJE 1031038081200 - INTEREST INCOME WHTExpensePLNon-CashCash
16-Aug-1816-Aug-18PrimaryDT 31042212200 - VAT INPUTAssetBSNon-Cash 
16-Aug-1816-Aug-18PrimaryDT 31044001100 - ACCOUNT PAYABLE - SUPPLIERSLiabilitiesBSNon-Cash 
16-Aug-1816-Aug-18PrimaryDT 31042212400 - PREPAID EXPENSESAssetBSNon-Cash 
13-Aug-1813-Aug-18PrimaryJE 1051054501600 - LOAN FROM BOD - OV4 THE CANALLiabilitiesBSNon-CashCash
13-Aug-1813-Aug-18PrimaryJE 1051051101163 - CASH IN BANK USD - CANADIA CURRENT INVEST - 1168AssetBSCash and cash equivalent 
14-Aug-1814-Aug-18PrimaryJE 1061064501600 - LOAN FROM BOD - OV4 THE CANALLiabilitiesBSNon-CashCash
14-Aug-1814-Aug-18PrimaryJE 1061061101163 - CASH IN BANK USD - CANADIA CURRENT INVEST - 1168AssetBSCash and cash equivalent 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dax to compare 2 rows and return result base on condition

@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")

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





2 REPLIES 2
Super User
Super User

Re: Dax to compare 2 rows and return result base on condition

@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")

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Highlighted
earsuyheng Frequent Visitor
Frequent Visitor

Re: Dax to compare 2 rows and return result base on condition

Dear Pattem,

 

Thanks for your solution.

 

Many thanks for your time and contribution.

 

Regards,

Heng