0 Replies Latest reply on Nov 26, 2018 2:30 PM by Andrew Carlson

    Using a Single Drop Down Filter for Two Identical Dimensions with Differing Relationships (SSAS Multidimensional)

    Andrew Carlson

      Hi Everyone!

       

      I've got a brain buster here and I'm hoping that someone can help.  I have two fact objects FactPerson and FactEvent.  FactPerson contains all of the current information pertaining to a group of people and is at the person granularity (no duplicate people).  FactEvent contains all of the person related events that can occur throughout a persons life and is at the event granularity (a person can have multiple events).  FactPerson and FactEvent are related through DimPerson which is also at the person granularity (DimPerson to FactEvent is one to many).  I also have two identical location dimensions.  One DimLocation(Current) is related to FactPerson on CurrentLocationID and there is a referenced relationship to FactEvent using DimPerson so that I can count all of the events related to a person based on where a person is currently located, regardless of where the event occurred.  The other DimLocation(Event) is related to FactEvent on EventLocationID so that I can count all of the events that occurred at a given location.

       

      If you're following me this far, then you might know where this is going...

       

      I am working on a dashboard that contains two sheets.  One sheet aggregates event data based on where the person is currently located, and the other aggregates event data based on where the events occurred.  I would like to avoid having two location drop down filters that each control different sheets (one for Current Location and the other for Event Location).  Since both dimensions have identical relevant values, is there any way to synchronize filters?  Or is there a way to create a filter action that will apply the same value to both target dimensions?

       

      I tried the parameterized work around (see Using Multiple Dimensions Under One Filter ) where you create a dimensional parameter which is a list of all dimension values and then set that parameter equal to both dimensions using two calculated fields, but it appears that you can not reference a dimension in a calculated field when you are using an SSAS data source... go figure.

       

      At this point, I have exhausted all of my resources and I'm at a loss.

       

      Any questions/feedback are greatly appreciated.  Typing out technical questions like this is not easy!