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

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'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 !!