Forum Discussion
Does someone know if bi-directional cross-filtering will appear in Power Pivot?
Like in SSAS 2016 and Power BI desktop.
Thanks
36 Replies
- 2021 and this is still an issue.
That's an issue but not showstopper, CROSSFILTER() works.
- 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.
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.
Yah, Power Pivot & Query are still relatively far behind from Power BI Desktop. Unfortunately. Hope they will be more close one day.
- Hi there, can you help me to explain the following excel formula please = FindDist(K33:K68,H33:H68,M33) I could not find this function in excel and what is basic rule of this function FindDist(x,A,B) Thanks
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
- Hi all I wanted to know if its possible to have an excell table scroll down as one scrolls down sort of like a floating table so data can bee seem as another table gets filled
Ashvini SharmaMicrosoft
It's not in Power Pivot as yet, hopefully soon. Ashvini Sharma ExcelAshvini Sharma, Olaf Hubel - any news here? And more general question - why DAX for Excel is far behind Power BI Desktop and even SSAS? My guess they use the same engine.
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.
Thank you
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/
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.
-Brett
- Ashvini, thank you for good news. CROSSFILTER() helps, but with built-in interface for bi-directional cross-filtering life will be bit more easier.
- I haven't checked, but it may be in the latest update for Excel. IT's the same engine as the other two products, just a couple of months behind.
- Nope, it's not here. Yes, the same engine, just open the interface, but nothing. Perhaps some compatibility issues.
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."
