Reply
zvm Regular Visitor
Regular Visitor
Posts: 26
Registered: ‎11-13-2017
Accepted Solution

PowerPivot in Excel and Power BI produce different join result

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.PNGPowerPivotModel

 

And here is the result in Pivot Table in Excel:

PivotTableReports.PNGPivot 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.

PowerBIReport.PNG

 

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. Smiley Happy 

Thanks! 


Accepted Solutions
Community Support Team
Posts: 1,361
Registered: ‎07-25-2018

Re: PowerPivot in Excel and Power BI produce different join result

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Community Support Team
Posts: 1,361
Registered: ‎07-25-2018

Re: PowerPivot in Excel and Power BI produce different join result

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
zvm Regular Visitor
Regular Visitor
Posts: 26
Registered: ‎11-13-2017

Re: PowerPivot in Excel and Power BI produce different join result

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