Forum Discussion

learning_dax's avatar
learning_dax
Helper II
3 years ago
Solved

Using Date Table Filtering by different relationships as slicer

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? 

 

2 Replies

  • 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's avatar
      learning_dax
      Helper II

      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!