PowerPivot Pivot Table Slicers no longer cross filtering

Michael Bekiares
Michael Bekiares used Ask the Experts™
on
I built a dashboard with a facts table and a few dimension tables loaded into the data model with relationships declared.
After some development, the slicers stopped cross filtering each other.
The slicers still work - they just don't affect each other's content anymore.
I don't recall changing any settings that would cause this.
Is this is corruption?
Anything I can do short of rebuilding?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Tom FarrarConsultant

Commented:
Are the slicer fields coming from the dimension tables?
Michael BekiaresBI Developer

Author

Commented:
Yes, they are.  If I use the keys from the data table as slicers instead, they DO cross filter.  But when I use slicers from the dimension tables they don't.  If I recreate the workbook, they work fine.  But that's a pain, of course.  Ideally I don't want to have to deal with this situation again.
Tom FarrarConsultant

Commented:
Did you build the dashboard using the Power BI tool?
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tom FarrarConsultant

Commented:
Is it possible to delete the slicer(s) and add it (them) back?
Tom FarrarConsultant

Commented:
Also, this link might be helpful in explaining how slicers work, and how they can be synced....

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers
Michael BekiaresBI Developer

Author

Commented:
This is PowerPivot in Excel, not PowerBI.  I have confirmed that it is related to the introduction of a specific measure.  As soon as that measure goes into a pivot table, the slicer breaks.  If I remove the measure, delete the slicer, then recreate it, things work again.
Consultant
Commented:
I don't know.  The only thing I can suggest is it may have something to do with the filtering related to the slicers.  To get correct filtering from your data model, the filtering must flow downstream, and this is generally from the dim lookup tables to the fact table(s).  Date fields should be filtered in the dim lookup table for instance.  Is it possible there is some confusion between the way the DAX formula is written?  Say the filtering in the formula is filtering the field from the wrong source?
Michael BekiaresBI Developer

Author

Commented:
Sorry for the long silence.  What's happening more precisely is that I have multiple slicers, each from different dimension tables.  Ideally when a filter is applied from a slicer, it should knock out all the options in the other slicers that are no longer logically available.  And it does indeed do that, until I introduce a specific complex measure.  I guess something in the measure formula is creating a break in the logical cohesion of the slicers.  I think I'll move on from this and revisit it again when I reach a deeper understanding of DAX.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial