powerbissasdaxmsbi

Get 1/0 if current column value exist in another table


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".


Solution

  • 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 )