Adding new field to Calculated Table

[kruzack]

Hi All,

I have created 2 calculated tables from my base data, and then I am joining the 2 tables using naturalleftouter join

In both tables I have Date, CustomerID and Status. Below is how the data structure is:

Left table:

Date	CustomerID	Status
31-May-23	1234	1
31-May-23	2345	2
31-May-23	3456	3

Right Table:

Date	CustomerID	Status
31-May-23	1234	2
31-May-23	2345	3

after doing the left join, I would get the below output

Date	CustomerID	Status Left Table	Status Right Table
31-May-23	1234	1	2
31-May-23	2345	2	3
31-May-23	3456	3

I wanted to know how to replace the null / blank value for the customer not present in right table, with a default value

All these calculations are done inside a measure.

Below is sample DAX code which I have written

Practice =

VAR fromtable = SELECTCOLUMNS(CollectionMonthlyDailyFrom,"loanno",CollectionMonthlyDailyFrom[LOANNO]&"","dpd_group",CollectionMonthlyDailyFrom[DPD Group],"report_date",CollectionMonthlyDailyFrom[REPORT_DATE], "DPD Group Num", CollectionMonthlyDailyFrom[DPD Group Num])

VAR totable = SELECTCOLUMNS(CollectionMonthlyDailyTo,"loanno",CollectionMonthlyDailyTo[LOANNO]&"","dpd_group_1",CollectionMonthlyDailyTo[DPD Group], "report_date_1",CollectionMonthlyDailyTo[REPORT_DATE], "DPD Group_1 Num", CollectionMonthlyDailyTo[DPD Group Num])

VAR Result = NATURALLEFTOUTERJOIN(fromtable, totable)

Result[DPD Group_1 Num] = IF(ISBLANK(Result[DPD Group_1 Num]), 1, Result[DPD Group_1 Num]) --- I am getting error here

RETURN countrows(filter(Result, [DPD Group_1 Num] = [DPD Group Num]))

Can some one please guide me through on this.

Thanks In Advance

[tamerj1]

Hi @kruzack 
Please try

Practice =
VAR fromtable =
    SELECTCOLUMNS (
        CollectionMonthlyDailyFrom,
        "loanno", CollectionMonthlyDailyFrom[LOANNO] & "",
        "dpd_group", CollectionMonthlyDailyFrom[DPD Group],
        "report_date", CollectionMonthlyDailyFrom[REPORT_DATE],
        "DPD Group Num", CollectionMonthlyDailyFrom[DPD Group Num]
    )
VAR totable =
    SELECTCOLUMNS (
        CollectionMonthlyDailyTo,
        "loanno", CollectionMonthlyDailyTo[LOANNO] & "",
        "dpd_group_1", CollectionMonthlyDailyTo[DPD Group],
        "report_date_1", CollectionMonthlyDailyTo[REPORT_DATE],
        "@DPD Group_1 Num", CollectionMonthlyDailyTo[DPD Group Num]
    )
VAR Result =
    NATURALLEFTOUTERJOIN ( fromtable, totable )
RETURN
    GROUPBY (
        ADDCOLUMNS ( Result, "DPD Group_1 Num", COALESCE ( [@DPD Group_1 Num], 1 ) ),
        [loanno],
        [dpd_group],
        [report_date],
        [DPD Group Num],
        [dpd_group_1],
        [report_date_1],
        [DPD Group_1 Num]
    )

Or you can try with GENERATE

Practice =
VAR fromtable =
    SELECTCOLUMNS (
        CollectionMonthlyDailyFrom,
        "loanno", CollectionMonthlyDailyFrom[LOANNO] & "",
        "dpd_group", CollectionMonthlyDailyFrom[DPD Group],
        "report_date", CollectionMonthlyDailyFrom[REPORT_DATE],
        "DPD Group Num", CollectionMonthlyDailyFrom[DPD Group Num]
    )
VAR totable = CollectionMonthlyDailyTo
RETURN
    GENERATE (
        fromtable,
        VAR CurrentLoanno = [LOANNO]
        RETURN
            SELECTCOLUMNS (
                FILTER ( totable, CollectionMonthlyDailyTo[LOANNO] & "" = CurrentLoanno ),
                "dpd_group_1", CollectionMonthlyDailyTo[DPD Group],
                "report_date_1", CollectionMonthlyDailyTo[REPORT_DATE],
                "DPD Group_1 Num", CollectionMonthlyDailyTo[DPD Group Num]
            )
    )

[kruzack]

Hi @tamerj1 

Thanks for the reply.

I tried both of the solution you provided. 
In both of them i need to create new tables. If I am creating new tables my dataset becomes too large, as the left join is happening on each date, from 500 K rows in actual tables it is going to 11 Million rows.

Thats why i am just using a measure to do the left join and then i am trying to add a new column to it

Below is what i tried to create a measure based on your input

Practice =

VAR fromtable = SELECTCOLUMNS(CollectionMonthlyDailyFrom,"loanno",CollectionMonthlyDailyFrom[LOANNO]&"","dpd_group",CollectionMonthlyDailyFrom[DPD Group],"report_date",CollectionMonthlyDailyFrom[REPORT_DATE], "DPD Group Num", CollectionMonthlyDailyFrom[DPD Group Num])

VAR totable = SELECTCOLUMNS(CollectionMonthlyDailyTo,"loanno",CollectionMonthlyDailyTo[LOANNO]&"","dpd_group_1",CollectionMonthlyDailyTo[DPD Group], "report_date_1",CollectionMonthlyDailyTo[REPORT_DATE], "DPD Group_1 Num", CollectionMonthlyDailyTo[DPD Group Num])

VAR Result = NATURALLEFTOUTERJOIN(fromtable, totable)

Result = ADDCOLUMNS ( Result, "DPD Group_1 Num", COALESCE ( Result[DPD Group_1 Num], 1 ) )

RETURN countrows(filter(Result, [DPD Group_1 Num] > [DPD Group Num]))

I am trying to add a column to the "Result" calculated table (coming from the left join)

but it is still giving me error.


Can you please let me know how it can be implemented within a measure.

Thanks in Advance

[tamerj1 · Accepted solution]

@kruzack 
For a measure please try

Practice =
VAR fromtable =
    SELECTCOLUMNS (
        CollectionMonthlyDailyFrom,
        "loanno", CollectionMonthlyDailyFrom[LOANNO] & "",
        "dpd_group", CollectionMonthlyDailyFrom[DPD Group],
        "report_date", CollectionMonthlyDailyFrom[REPORT_DATE],
        "DPD Group Num", CollectionMonthlyDailyFrom[DPD Group Num]
    )
VAR totable =
    SELECTCOLUMNS (
        CollectionMonthlyDailyTo,
        "loanno", CollectionMonthlyDailyTo[LOANNO] & "",
        "dpd_group_1", CollectionMonthlyDailyTo[DPD Group],
        "report_date_1", CollectionMonthlyDailyTo[REPORT_DATE],
        "@DPD Group_1 Num", CollectionMonthlyDailyTo[DPD Group Num]
    )
VAR Result =
    ADDCOLUMNS (
        NATURALLEFTOUTERJOIN ( fromtable, totable ),
        "DPD Group_1 Num", COALESCE ( [@DPD Group_1 Num], 1 )
    )
RETURN
    COUNTROWS ( FILTER ( Result, [DPD Group_1 Num] > [DPD Group Num] ) )

[kruzack]

@tamerj1 

Thanks for the solution it works perfectly !!

[some_bih]

Hi @kruzack you wrote "after doing the left join" - this is "issue"

Left join finds all rows from Left table and matched it with right table.

In your example it seems that you need different type of join? Hope this help