Relationship/Slicer Issue

Hi guys
Been a while since I’ve been on site. Too much non dev financial work going on in the CFO world!!
And also thanks to Sam I got the inspiration I needed earlier in the year to create a financial reporting tool for our business. All going well and now getting towards the end of the project.

However I am struggling to manage a relationship issue in a financial reporting model:

  1. Fact table with RepLine, Cost Centre, Stream(Bud or Act), Amount, dates
  2. Dim table for cost cent linking one to many to fact table
  3. Dim table for site and Dim table for operation (dept) each linking one to many with cost cent
  4. Dim Report Spec table with RepLine (linked back one to many with Fact table), Line description, Report Name etc
    This all works beautifully for a 9 site motor group with multiple departments.
    Most of my report pages have a 3 slicers, site, dept and Report name. - All working well.
    Next steps are to bring manager lists in. So:
  5. Dim Manager List with 2 columns - Manager and Cost Centre linked Many to one to CostCent table
    All working well here but needed to set direction to both for Manager List, Dept and site tables.
    When I bring in a slicer for manager the Site and Dept filters are behaving properly, ie only showing the sites and departments for which the manager has responsibility.

I am now trying to shrink down the selections on the Report Name Slicer to only those reports relevant to a given dept. But this is proving very difficult. My report Spec table has a column for dept name eg vehicle Sales gross, service/bodyshop gross, parts gross, expenses. There are also some summary depts. covering a whole site. The reason for shrinking down the slicer is not solely cosmetic however.
Once set up correctly the cube will ten lend itself to generating paginated reports more easily without the need for lots of fiddly LookUp functions

There is “kind of” a relationship between operations and the the dept name in the Spec table, which I have tried to set up and create relationships for but the slicer for report is not shrinking down when clicking on a manager???

Please see attached “watered down” version of my pbix with test data for a boat sales, repairs and chandlery business. The manager slicer is working correctly, but I am trying to reduce the MA report slicer when filtering Buzz only New Boat sales will appear; when filtering Combat Karl only Boat repairs will appear; when filtering Andy all the reports will appear.

Apologies for long preamble, sometimes it’s difficult to explain your problem without background and examples.
Hope you can help
PeteBoatTest.pbix (296.0 KB)

The quickest possible solution is set the filter to bi-directional from MAReports, to MAFact

If that is not an option, or isnt correct, will have to really spend some time data modelling

Thanks For assist Nick, But it didn’t work.
I’ve also tried a TREATAS option - (didn’t work) but really still have no idea where I’m going with this.
Whilst your suggestion on spending time data modelling is fine, I’ve no idea how to model this to get to my end goal!

Hi Pete,

Looks like you should really update this model and simplify it. Once you do this, I’m confident a solution will become clearer.

Go through this course when you can as this shows you exactly how to set up you model effectively.


Hi Sam
Sorry taken so long to respond. Very much a back burner project for a busy CFO! I have looked at some of your videos and picked up some great tips on cascading the model structure which have assisted me to speed up the response times for data refresh. Whilst this hasn’t fixed the problem I logged they were just awesome tips.
The logged problem with a bidirectional hierarchy still remains - ie multiple managers with multiple depts. I have a few ideas to try, but for the moment I would close this one off your end. Might be some time before I get hands to keyboard on it. I’ll recontact if I get stuck!
Thanks again