Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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] ) )
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
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] ) )
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
Proud to be a Super User!
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
9 | |
6 |