Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
kruzack
Frequent Visitor

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:

DateCustomerIDStatus
31-May-2312341
31-May-2323452
31-May-233456

3

 

Right Table:

DateCustomerIDStatus
31-May-2312342
31-May-2323453


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

DateCustomerIDStatus Left TableStatus Right Table
31-May-23123412
31-May-23234523
31-May-2334563 


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

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

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

 

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

@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 

Thanks for the solution it works perfectly !!

 

some_bih
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.