How to conditional relate to another table

[rs1249]

Dear all,

Could someone help me with this please:

I have two tables: "Detailed_Cust_Ledg" and "Cust_Ledg", they are linked by one to many relationship with Cust_Ledg[Entry_No]-> Detailed_Cust_Ledg[Cust_Ledger_Entry_No], 1->*

Now I would like to create a calculated column in Detailed_Cust_Ledg[Index], with DAX something like:

Index = if([Entry_Type]="Initial Entry",RELATED(Cust_LedgerEntries[Index]), #else use the same Cust_LedgerEntries[Index]) that belongs to same [Document_No] with [Entry_Type]="Initial Entry"#)

I just can't figure out how to do the else statement in there, driving me nuts, would much appreciated if anyone can help, here are the two tables:

Detailed_Cust_Ledg

Entry_No	Posting_Date	Entry_Type	Document_Type	Document_No	Customer_No	Amount	Cust_Ledger_Entry_No	AppliedDocNo	Index
996	06/12/2017	Initial Entry	Payment	BANK-0000365	C0000050	-432	7861	BANK-0000365	#ERROR
997	06/12/2017	Application	Payment	BANK-0000365	C0000050	-432	7680	INV-0000299	#ERROR
998	06/12/2017	Application	Payment	BANK-0000365	C0000050	432	7861	BANK-0000365	#ERROR
1968	14/02/2018	Initial Entry	Payment	BANK-0000594	C0000050	-120	10979	BANK-0000594	#ERROR
1969	14/02/2018	Application	Payment	BANK-0000594	C0000050	-120	7100	INV-0000205	#ERROR
1970	14/02/2018	Application	Payment	BANK-0000594	C0000050	120	10979	BANK-0000594	#ERROR
1990	22/02/2018	Initial Entry	Payment	BANK-0000594	C0000050	-3360	11005	BANK-0000594	#ERROR
1991	22/02/2018	Application	Payment	BANK-0000594	C0000050	-1680	5717	INV-0000065	#ERROR
1992	22/02/2018	Application	Payment	BANK-0000594	C0000050	3360	11005	BANK-0000594	#ERROR
1993	22/02/2018	Application	Payment	BANK-0000594	C0000050	-1680	6873	INV-0000140	#ERROR
1997	07/03/2018	Initial Entry	Payment	BANK-0000595	C0000050	-342	11009	BANK-0000595	#ERROR
1998	07/03/2018	Application	Payment	BANK-0000595	C0000050	-342	7010	INV-0000183	#ERROR
1999	07/03/2018	Application	Payment	BANK-0000595	C0000050	342	11009	BANK-0000595	#ERROR

 Cust_Ledg

Entry_No	Customer_No	Posting_Date	Due_Date	Document_Type	Document_No	Amount	Index
7861	C0000050	06/12/2017	06-Dec-17	Payment	BANK-0000365	-432	BANK-000036506/12/2017C0000050STRIPE PAYMENTS UK E-01461432
10979	C0000050	14/02/2018	14-Feb-18	Payment	BANK-0000594	-120	BANK-000059414/02/2018C0000050STRIPE PAYMENTS UK120
11005	C0000050	22/02/2018	22-Feb-18	Payment	BANK-0000594	-3360	BANK-000059422/02/2018C0000050BARCLAYS BANK PLCP3360
11009	C0000050	07/03/2018	07-Mar-18	Payment	BANK-0000595	-342	BANK-000059507/03/2018C0000050STRIPE PAYMENTS UK342

[ryan_mayu]

@rs1249 

it looks like there are not matching entry number, 

 if([Entry_Type]="Initial Entry",RELATED(Cust_LedgerEntries[Index]) will return empty for the column

what's the expected output based on your sample data?

[rs1249]

Hi Guys, the relationsship is Detailed_Cust_Ledg[Cust_Ledger_Entry_No]->Cust_Ledg[Entry_No] *->1

on the top table the 7th Column is matched to botton table 1st column, so the end result should be in the top table last column, it should pulling in the index value from the bottom table index column, but it looks at the top table's 3rd column "Entry_Type" and 7th Column "Cust_Ledger_Entry_No", doing something like this:

If 3rd column [Entry_Type] value is "Initial Entry" Lookup (Cust_LedgerEntries[Index]) base on 7th Column "Cust_Ledger_Entry_No", if not, copy the  same Cust_LedgerEntries[Index]) that is above until the next [Entry_Type]="Initial Entry"

Hope that make sense

[ryan_mayu · Accepted solution]

@rs1249 

you can try this

Column = 
 if(Deatiled_Cust_Ledg[Entry_Type]="Initial Entry",LOOKUPVALUE('Cust_Ledg'[Index],Cust_Ledg[Entry_No],'Deatiled_Cust_Ledg'[Cust_Ledger_Entry_No]))

Column 2 = 
if(Deatiled_Cust_Ledg[Column]="", maxx(FILTER('Deatiled_Cust_Ledg','Deatiled_Cust_Ledg'[Document_No]=EARLIER(Deatiled_Cust_Ledg[Document_No])&&Deatiled_Cust_Ledg[Entry_No]<=EARLIER('Deatiled_Cust_Ledg'[Entry_No])&&Deatiled_Cust_Ledg[Column]<>""),Deatiled_Cust_Ledg[Column]),Deatiled_Cust_Ledg[Column])

pls see the attachment below