Aug 02 2016
11:56 AM
- last edited on
Jul 12 2019
10:38 AM
by
TechCommunityAP
Aug 02 2016
11:56 AM
- last edited on
Jul 12 2019
10:38 AM
by
TechCommunityAP
Like in SSAS 2016 and Power BI desktop.
Thanks
Jan 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
Feb 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.
Feb 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.
Jun 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.
Jul 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.
Thanks,
Howie Dickerman
Jul 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.
Jul 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.
May 19 2021 04:27 AM
May 19 2021 04:34 AM
That's an issue but not showstopper, CROSSFILTER() works.
May 19 2021 04:54 AM
May 19 2021 10:14 AM
Depends on goals and on model. In general it's better to avoid bi-directional where possible. For example Bidirectional relationships and ambiguity in DAX - SQLBI and some other recommendations.
May 19 2021 11:15 AM
@Sergei Baklan That is true, but Power Pivot lacks the ability to filter slicers based on Measure Filters, which is the best way to avoid Bi-Di in Power BI.
The answer to Bi-Di is rarely, but it isn't never.
I hate to see Power Pivot getting stale like this. Bi-Di is just the tip of the iceberg. I'd kill for ISINSCOPE() vs having to fool with HASONEFILTER/HASONEVALUE trickery to keep/suppress totals and such.
May 19 2021 01:19 PM
Another point is negative impact on performance.
But yes, of course, there is no such things which never to use, or ones which only to use. Depends on data characteristics and model.
I don't think Power Pivot ever will be full scale tool. But perhaps that's not critical these days, at least for corporate environment.
Jul 19 2023 05:25 AM
Hi @Sergei Baklan ,
I followed your instructions but in the moment in which I open the updated Excel file and try to open the excel data model, an alert appears:'Impossible for PowerPivot to upload the data model'. I read that could be incompatibility problems between versions of Excel/Power BI. Do you know a workaround to apply in order to achieve the result?
Many thanks in advance,
Nate.
Jul 19 2023 11:53 AM
Most probably data model is corrupted, if only your previous version was not 2010. Please check "PowerPivot is unable to load the data model" error - Microsoft Community if helps.
Jul 19 2023 11:52 PM
Jul 20 2023 07:15 AM
@NateP86 , okay. For future, that's a separate question, it's much better to start new discussion with your question. More chances that you have more answers.