PowerBI Natural Left outer join issues with deleted rows in right table

3
votes

I have two tables.

1) Table 1 : 1 column with date value
2) Table 2 : 2 columns : Date column + business value column

I am trying to use DAX in PowerBI to create a new table using a left outer join to fill missing dates in my second table.

First table :

| Date       |  
| 2015-05-01 |
| 2015-06-01 |
| 2015-07-01 |       
| 2015-08-01 |

Second table :

| Date       | Value    | 
| -----------|--------- |
| 2015-05-01 |        5 |    
| 2015-05-01 |        5 |    
| 2015-06-01 |        6 |    
| 2015-07-01 |        7 |

DAX code to create new table :

Table = 
var table4=
SELECTCOLUMNS(Table1, "Date", Table1[Date]&"")
var table5=
SELECTCOLUMNS(Table2,"value", Table2[value],"Date", Table2[Date]&"")
return
NATURALLEFTOUTERJOIN(table4,table5)

This is returning :

| Date       | Value    | 
| -----------|--------- |
| 2015-05-01 |        5 |    
| 2015-06-01 |        6 |    
| 2015-07-01 |        7 |    
| 2015-08-01 |        NA|

But I want:

| Date       | Value    | 
| -----------|--------- |
| 2015-05-01 |        5 |    
| 2015-05-01 |        5 |    
| 2015-06-01 |        6 |    
| 2015-07-01 |        7 |    
| 2015-08-01 |       NA |

I am not sure why it is removing the second value of

| 2015-05-01      5|

I need the two values for the month of may to remain in the table. Any ideas ? thanks a lot

2
left-joinpowerbidax
show your real script. – Vijunav Vastivch
What does DAX Studio trace tell you? PS A minimal reproducible example includes cut & paste & runnable code including intialization so it is easy for others to reproduce your example & to show your really got claimed output from claimed input. – philipxy
@philipxy thanks for your comment. I haven't used Dax studio yet but will download it now to see if I get any messages. As for the minimal reproducible example, i entered the data manually in PowerBi to create Table 1 and 2. Then, to merge the two tables together, I went to the modelling tab, clicked on new table and used the DAX code I posted in the question. Is there anything missing to reproduce this example? thanks again! – ed14

2 Answers

2
votes

I have contacted Microsoft development team in August, 2019 (via Marco Russo at SQLBI); they confirmed that this behavior was caused by a bug, and promised to fix it in the upcoming releases.

I have tested November, 2019 release of Power BI Desktop and confirm that the bug is indeed fixed.

My test code:

T1 = DATATABLE("Date", INTEGER, {{1}, {2}, {3}, {4}})

T2 = DATATABLE( "Date", INTEGER, "Value", INTEGER, {{1, 5}, {1,5}, {2, 6}, {3, 7}})

Test = 
  VAR T3 = SELECTCOLUMNS(T1, "Date", T1[Date]*1)
  VAR T4 = SELECTCOLUMNS(T2, "Date", T2[Date]*1, "Value", T2[Value])
RETURN 
  NATURALLEFTOUTERJOIN(T3, T4)

Results:

enter image description here

-1
votes

As documented: https://docs.microsoft.com/en-us/dax/naturalinnerjoin-function-dax

It joins on the natural columns the rows which matches both and then adds extra columns.

You need to ask yourself why you need this row in your data because when your model is correct, Power-Bi will do the work for you. In your situation you would generate one extra row for each date where a value does not exist, do you need this?

I created your use case and made the correct model: enter image description here

next I created a table with dates and values and selected on Date: Select values with no date:

enter image description here

It is just how you want to present your data.. I do a summary over the values which fall on the same date..