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

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

5 Replies

some_bih
Community Champion
3 years ago
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

tamerj1

Community 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
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
  Community 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] ) )

Forum Discussion

Adding new field to Calculated Table

5 Replies

Related content

Adding filters to a calculated column

Create Calculated Fields based on Field Parameter selected

Filter Field Parameter by Field Parameter Values without Calculated Groups

Tree Map adding fields

Adding columns