Does someone know if bi-directional cross-filtering will appear in Power Pivot?

[Sergei Baklan]

Like in SSAS 2016 and Power BI desktop.

Thanks

[#2]

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

[#3]

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.

[#4]

Yah, Power Pivot & Query are still relatively far behind from Power BI Desktop. Unfortunately. Hope they will be more close one day.

[#5]

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

[#6]

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

[#7]

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.

[#8]

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

[#9]

2021 and this is still an issue.

[#10]

@tsimard 

That's an issue but not showstopper, CROSSFILTER() works.

[#11]

So I need to create a DAX measure for every possible measure I want to crossfilter? No thanks. I'll do it someplace other than excel instead.

[#12]

@tsimard 

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.

[#13]

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

[#14]

@Ed Hansberry 

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.

[#15]

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.

[#16]

@NateP86 

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.

[#17]

Thanks for replying. I will try different versions of Excel then and see what happens :)

[#18]

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