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

问题描述

我有两个桌子.

1)表1:1带有日期值的列
2)表2:2列:日期列+商业价值列

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

我正在尝试在PowerBI中使用DAX通过左外部联接创建新表，以填充第二个表中的缺失日期.

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.

第一张桌子:

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

第二张表:

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

DAX代码以创建新表:

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)

这正在返回:

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

但我想要

| 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

推荐答案

我已于2019年8月(通过Marco Russo在

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.

我已经测试了Power BI Desktop的2019年11月版，并确认确实修复了该错误.

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

我的测试代码:

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)

结果:

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