Using Date Table Filtering by different relationships as slicer

[learning_dax]

Hi all, 

I've created a DimDate table based on many different calculated columns. I have custom Fiscal Year & Fiscal Quarter columns to parse out the reporting based on Fiscal Calendar rather than Calendar year. Below are my relationships in my model. Right now, I use my date table slicers by Fiscal Quarter and Fiscal Year to filter my entire dashboard by ShipDate, since that is my active relationship. However, I don't know how to filter my dashboard by different date parameters based on inactive relationships using the same slicers by Fiscal Year/Fiscal Quarter. For example, in my picture below I'd like to use similar slicers perhaps on a different page but based on Return Date instead of Ship Date. I know how to create a measure and show ShipDate info in a table based on Fiscal Quarters by using USERELATIONSHIP but I need these inactive relationships to filter as slicers, and I'm not sure how to do that. Any help on how to overcome this? 

 

[TomMartens · Accepted solution]

Hey @learning_dax ,

the challenge you are facing has a name - this is good 🙂 The name is roleplaying dimensions.
Your requirement can not be solved by using a single date table and inactive relationships, instead, you need multiple date tables (populating multiple slicers) . An introduction to this concept is given by this article: Welcome to Power BI theatre: Role-Playing Dimensions! - Data Mozart (data-mozart.com).

Hopfully, this gets you started.

Regards,

Tom

[learning_dax]

Hi Tom,

Thanks for the quick response! Great to know that this is a popular challenge & that there is ample information to find online! Thanks for the article, and I'll start researching on the topic. Thank you!