cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zvm Regular Visitor
Regular Visitor

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

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.
2 REPLIES 2
v-cherch-msft Super Contributor
Super Contributor

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

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 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 365 members 3,973 guests
Please welcome our newest community members: