Forum Discussion

kruzack's avatar
kruzack
Frequent Visitor
3 years ago
Solved

Adding new field to Calculated Table

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...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
kruzack
Frequent Visitor
3 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    kruzack
    Frequent Visitor
    3 years ago

    tamerj1 

    Thanks for the solution it works perfectly !!