Cross Filtering Question

[chrisco]

I'm trying to build a Profit and Loss for my company that is scalable. I've used the "Cascading Subtotals" post from the PowerPivotPro forum here: ( Profit & Loss–The Art of the Cascading Subtotal - PowerPivotPro)

The problem I'm running into is with my slicers and the ability to cross filter. We have multiple Divisions, which have multiple Regions, each with multiple Business Units.

I would like the ability to select a Division, Region or Business Unit and the other 2 Slicers cross filtered. For some reason, this isn't happening. I've ensured that the cross filtering is enabled via slicer settings.

My relationships are:

FactTable[BusinessUnits] > dimAssignments[BusinessUnits]
dimAssignments[Divisions] > dimDivisions[Division] *
dimAssignments[Regions] > dimRegions[Regions]*

*field is selected for Slicer.

The other slicer is dimAssignemnts[BusinessUnitText].

 

[ImkeF]

In Power Pivot, filters normally don't flow "uphill" - meaning from the many side back to the 1 side of the relationship unless you adjust your measures: Filters CAN Flow Up Hill – Via Formulas That Is - PowerPivotPro
I'd think in your case it would be much easier to denormalize your Dim/Lookup-tables. Then you have all filtercolumns in one table and cross filtering would work without touching the existing measures.

 

[chrisco]

I'm not sure how that applies. The dimensions I am using for slicers aren't included in measures. They are also the "one" side of the "one to many" relationship. Am I misunderstanding your response?

 

[ImkeF]

I was just guessing that the dimensions under each other also had 1:n relationships - sorry, should have been clearer. It looks in your example that merging the DimDivisions and DimRegions into the DimAssignments would do what I meant. Then you'd have a start schema where all your desired dimension attributes sit in one table.

 

[chrisco]

After your suggestions, the relationships are set up like this. It still doesn't allow the slicers to cross-filter though.

I've had a few glitches where the program would crash or I would lose auto-recover (before any changes). I'm wondering if this caused a problem.

 

[chrisco]

Well that didn't work. Can't seem to upload a jpg.

 

[ImkeF]

Have a look at this file: https://www.dropbox.com/s/08embvl3z5tigb3/Kopie von PL-Example-Stage-1.xlsx?dl=0

There seems to be an issue with the slicers in that example (selections are greyed out - crossfilters will disappear). Solved it by creating a simple Pivot that is connected to the same slicers (you could hide that). But check out if all figures come out correctly - didn't do that myself.

& Don't ask why....

Somewhere I read that crossfiltering costs performance - but cannot remember where.

 

[chrisco]

I wasn't able to check out the file (I'm guessing my office internet security), but I took the idea you mentioned and linked the Slicers to a very simple pivot.

It worked!

I've read the same thing regarding cross-filters costing performance. Its in the Dax Formulas for Power Pivot book as well as Rob Collie's blog. I'm sure it's elsewhere, but those are the resources I have at hand. I believe its because each slicer has to re-query the data to perform a "cross-filter". I'm at a 1MM rows of data so I was concerned if that was a the issue.

Thank you for your help!