- last edited on
Like in SSAS 2016 and Power BI desktop.
08-03-2016 11:52 AM
08-03-2016 12:05 PM
08-05-2016 05:39 AM
Right now found in discussion under this Many to Many Relationships in Power BI and Excel 2016 blog Marco Russo's answer on the question why bi-directional is omit in 2016:
"Because it would have changed the binary format of Power Pivot model, making the file incompatible with Excel 2013. They wanted to avoid the issue existing between Excel 2010 and Excel 2013 when you have a data model. You have to upgrade to open it in 2013, and after that you cannot open it in 2010. So for Excel 2016 they wanted to keep full compatibility of binary file format."
08-08-2016 06:17 AM
Nice spot - thanks. I'd like to see where this lands - it's a pretty fundamental incompatibility. The portable nature of Power Pivot is what makes it particularly compelling.
08-09-2016 03:00 AM
Can't say i understand that issue with binary model. Done small exersize - created two identical simple models in pbix and Excel, the only difference pbix has bi-directional relationship and Excel one-to-many. Unzip both; extract DataModel from pbix; rename as item.data and replace one within the Excel. When open the updated Excel file (yes, there were some alerts) - and PowerPivot shows bi-directional within it's model. Slicer works from many-to-one.
10-07-2016 04:39 PM
10-08-2016 02:48 AM
10-08-2016 03:18 PM
Just wanted to also say thank you for this feedback on such an important feature.
I totally understand if there will be differences between PBIX and XLSX, particularly on the visualization layer, but I'd hope that the data retrieval, shaping/transforms, and modeling features stay closely aligned such that it's easier to move between the tools and choose the right one for the given scenario.
02-15-2017 10:57 AM
For example, recently introduced TREATAS() for Power BI desktop looks like on hold for Power Pivot.
My question is practical - for more or less complex models i prefer to build them in Excel and only on final stage to shift on Power BI Desktop (or directly publish to Power BI services). Above incompatibilities push me to switch directly on Power BI Desktop, but that's not what i'd like to do.
02-15-2017 11:07 AM
I would think that the fast update path is Excel 2016 on Office 365, but I haven't seen the roadmap. There are FILTER and INTERSECT alternatives for TREATAS() as mentioned here ... https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
02-15-2017 11:19 AM
Mark, i checked the roadmap few days ago (new one) and found nothing. But that doesn't matter. My question is more general - if Power Pivot is considered as legacy tool by Microsfot perhaps i'll shift one day on Power BI Desktop. Even if i don't like this and Excel is much more comfortable for me.
Yes, why i re-initiated this post since i read mentioned article.
03-13-2017 02:35 PM
I seem to have missed this thread.
Power Pivot isn't legacy, it's a core part of Excel analytics going forward. We're working on improving the quality of experience with the data model & Power Pivot, and for those on subscription, you'll start experiencing slight improvements, initially ~invisible, more focussed on quality.
Part of the work we're doing is to make sure the data model works well with across different versions Excel.. given the wide deployment cycles, we think it's necessary that one person using the latest version of Power Pivot doesn't suddenly cause others downlevel to have data model load failures. As you can imagine, this is substantial work. Once that foundation is laid, updates should be more frequent, we can't wait for that day!
03-14-2017 02:33 AM
Thank you for the clarification, i see the point.
One of the reasons why i'm asking such questions is the lack of information from Microsoft about what's going on with Power Pivot for Excel. Or i simply don't know how to find it.
For example, latest update was announced on blogs.office.com about an year ago. O365 Roadmap shows only two very aged records with Power Pivot. Similar is on TechNet.
Anyway, good luck with the foundation and waiting for the new updates.
04-29-2018 03:30 PM - edited 04-29-2018 03:31 PM
Is there any news on Bi-Directional Cross Filtering coming to PowerPivot for Excel. This is an extremely painful shortcoming--made worse by the perception that the technology is ready and waiting but just not being released. I check constantly for updates on this. Excruciating.
04-30-2018 08:56 AM
Now that the Excel 2019 Preview is released can you elaborate on PowerQuery and PowerPivot enhancements included in Excel 2019?
04-30-2018 09:10 AM
Since my last update, for Power Pivot, the bulk of our energy has been focused on quality improvements as well as supporting simultaneous coauthoring of BI objects like PivotTables. All of these are available for subscription customers. We have a little more of these remaining, and I look forward to then updating the database compat level to support new modeling features. Note that we've been shipping new versions of the engine periodically, so new DAX functions should be available.
For Power Query, there've been a stream of continuous updates for subscription customers.
To simplify, think of Excel 2019 ("perpetual release") as a snapshot of what we've been shipping to subscription customers rolled up into a release. For Power Query, this would include it being the new default, new transformations, column by examples, etc. For Power Pivot, this is mainly quality improvements, updated dialog for relationships creation, printing support in diagram, updated linked tables, updated measures editing experience, etc. In addition, there's a pleasant surprise for most folks (hint: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9018823-...
The best place to keep up with what's new is on our what's new pages: https://support.office.com/en-us/article/what-s-new-in-excel-2016-for-windows-5fdb9208-ff33-45b6-9e0...
Lead Program Manager
07-30-2018 08:50 AM
01-30-2019 04:02 PM
01-31-2019 01:08 AM
Hi @Rana786 ,
That's a separate question. Please go here https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral and click Start a New Conversation - you'll have more chances if someone answers
02-07-2019 05:29 PM
Wow. We are 2.5 years after the OP, and still no bi-directional filtering. I asked the Excel team in a Nov/Dec AMA on Reddit if they had plans to bring Power Pivot and Power Query up to speed with the same features in Power BI and they said they were going to make an effort to get them close to feature parity. I would hope to see some of that in 2019. I did see where Power Query last week in my Insider build got some Split Column functionality that Power BI doesn't have, so maybe things are starting to happen.
02-08-2019 12:42 AM
Yah, Power Pivot & Query are still relatively far behind from Power BI Desktop. Unfortunately. Hope they will be more close one day.
06-29-2019 02:10 PM
@Steven Weydert another day shot developing a custom DAX work-around to create bi-directional cross filtering in Power Pivot to handle an M2M scenario. The familiar tricks (e.g., crossjoin not working). Should be a one-click solution as has been the case for years in Power BI. No words for the level of frustration.
07-01-2019 11:49 AM
We would love to add this functionality to Power Pivot, but we have had to balance the benefit of adding this feature against the compatibility problems that it would create. The database engine can support this feature when run at a higher compatibility level setting than is currently used in Excel 2013, 2016, and 2019. By increasing the compatibility level setting, we would make this feature work, but existing versions of Excel would then be unable to load the data model from any workbook that uses the newer feature(s). Our customers expect (and require) that Excel workbooks can be sent to anyone in the world, and that the recipients will be able to open and run the workbook, even if they have an older version of Excel. Breaking this customer expectation is something we take quite seriously, and so far the benefits of this feature have not been sufficient to justify the pain associated with a compatibility break. Power BI Desktop doesn't have this issue, because they can insist that everyone install a free upgrade to the latest version whenever something is introduced that breaks compatibility.
I expect that eventually we will have sufficient new features and reasons to justify a compatibility break, and expect that we will update the feature set at that time. I don't know precisely when that will occur, and as part of that work we will need to do some extra compatibility work to make sure we have a solid story for all of our customers, so that existing business processes aren't overly disrupted by the change.
I hope this helps to explain why this feature (and a couple of others like it) aren't showing up yet in Excel.
07-02-2019 12:30 PM
Thanks for the response. I totally understand the need for backwards compatibility, but am equally frustrated that backwards compatibility sometimes means "Instead of this breaking for some users on older versions of Excel, we'll make it so no one can do it."
I think most people working in the Power Pivot data model at that level would understand that older versions may not allow all users to refresh or play with the model. Just like those of us playing with the new Dynamic Array functions, or Stock/Geography data types. It just gives garbage to non-365 users.
07-17-2019 05:49 PM
@Howie Dickerman I appreciate the response. As has been noted, the idea of holding back the most invested users is exceptionally frustrating due in part to the number of years we've been waiting. However, it would be even worse if you weren't at least willing to take the time to provide an update, however, disappointing.