First of all, I wrote the following Dax expression to get a table with the list of customers who got more than one loan.
VAR table_filter =
FILTER (
SUMMARIZE (
SUMMARIZE (
FACT_ACCOUNT;
FACT_ACCOUNT[ID_LOAN_INFORMATION];
FACT_ACCOUNT[ID_COSTUMER]
);
FACT_ACCOUNT[ID_COSTUMER];
"abc"; COUNTROWS (
SUMMARIZE (
FACT_ACCOUNT;
FACT_ACCOUNT[ID_LOAN_INFORMATION];
FACT_ACCOUNT[ID_COSTUMER]
)
)
);
[abc] > 1
)
The following picture represent the result of the previous Dax expression table_filter.
The result of table_filter variable
Next, I wrote this expression to get a column with the list of ID_COSTUMER from the previous table
VAR column_filter =
SELECTCOLUMNS ( table_filter; "ClientYes"; FACT_ACCOUNT[ID_COSTUMER] )
Finally, I used this expression to create a calculated column in the FACT_ACCOUNT table, if I found the value of the current ID_COSTUMER in the column_filter I put 1 else 0.
RETURN
IF (
CALCULATE (
COUNTROWS ( FACT_ACCOUNT );
FILTER (
FACT_ACCOUNT;
FACT_ACCOUNT[ID_COSTUMER] = EARLIER ( column_filter )
)
) > 0;
1;
0
)
The problem is that the result of column_filter is not a column I need a solution to return the FACT_ACCOUNT[ID_COSTUMER] column from the result of the first filtered table
I got the following error "the first argument of earlier/earliest is not a valid column reference in the earlier row context".
IsRenewal :=
VAR Filter_Table =
SUMMARIZE (
FILTER (
SUMMARIZE (
SUMMARIZE (
FACT_ACCOUNT;
FACT_ACCOUNT[ID_LOAN_INFORMATION];
FACT_ACCOUNT[ID_COSTUMER]
);
FACT_ACCOUNT[ID_COSTUMER];
"abc"; COUNTROWS (
SUMMARIZE (
FACT_ACCOUNT;
FACT_ACCOUNT[ID_LOAN_INFORMATION];
FACT_ACCOUNT[ID_COSTUMER]
)
)
);
[abc] > 1
);
FACT_ACCOUNT[ID_COSTUMER]
)
VAR Latest =
FILTER (
Filter_Table;
FACT_ACCOUNT[ID_COSTUMER] = EARLIER ( FACT_ACCOUNT[ID_COSTUMER] )
)
RETURN
IF ( ISBLANK ( Latest ); 0; 1 )