topic Re: Adding new field to Calculated Table in DAX Commands and Tips

Adding new field to Calculated Table

kruzack — Sun, 20 Aug 2023 09:52:06 GMT

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

Re: Adding new field to Calculated Table

some_bih — Sun, 20 Aug 2023 10:42:18 GMT

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

Re: Adding new field to Calculated Table

tamerj1 — Sun, 20 Aug 2023 18:39:25 GMT

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

Re: Adding new field to Calculated Table

kruzack — Mon, 21 Aug 2023 08:09:56 GMT

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

Re: Adding new field to Calculated Table

tamerj1 — Mon, 21 Aug 2023 08:53:48 GMT

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

Re: Adding new field to Calculated Table

kruzack — Tue, 22 Aug 2023 13:09:44 GMT

tamerj1

Thanks for the solution it works perfectly !!