PowerBI 自然左外连接问题与右表中的已删除行 [英] PowerBI Natural Left outer join issues with deleted rows in right table

问题描述

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

解决方案

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:

这篇关于PowerBI 自然左外连接问题与右表中的已删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！