PowerPivot in Excel and Power BI produce different join result

[zvm]

Hi,

I have noticed one strange behaviour of a join in PowerPivot for Excel. It is Office365 Excel.

Here is my simple PowerPivot model and table content.

PowerPivotModel

And here is the result in Pivot Table in Excel:

Pivot Table

As you can see, I get Cartesian product as if there is no join defined if I put only attribute columns on a report.

But if I add column that can be aggregated (measure Actual) then everything is fine!

The same model in Power BI works correctly in every case.

So, what is the reason for such an behaviour in Excel? Does it have anything to do with Excel actaully generating MDX query agianst data source? Whatever is the case, I see it as a bug. 🙂 

Thanks! 

[v-cherch-msft · Accepted solution]

Hi @zvm

You may have a look at below similar post.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9241a547-86b1-4eed-8b92-aece6bff7739/table-...

Regards,

Cherie

[zvm]

Hi Cherie,

Thank you. That explains the reason for such a behaviour in Power Pivot, with one small remark from my side: I do not try to connect multiple dimensions through the fact, but only one fact and one dimension. But that does not matter...

Why is then Power BI different? Isn't the in the essence the same engine?

Regards,

Zrinko