Forum Discussion

jscottNRG's avatar
jscottNRG
Icon for Helper II rankHelper II
8 years ago
Solved

Single date slicer to filter multiple date tables

I'm hoping this isn't actually so difficult, but I've had a tough time trying to figure out the proper setup. I'd like to use a single date slicer to filter multiple tables with dates in them. The ta...
Need Help
  • edhans's avatar
    edhans
    8 years ago

    Shouldn't your slicer be based on the dates in a Date Table where the Date field is related to all of the relevant date fields in your other tables?

Aree's avatar
Aree
Icon for Resolver I rankResolver I
8 years ago

You would use a Date Table as the main source that all timephased data would be linked too.

 

The typical functions used to create this table is the following DAX
CALENDAR(<start_date>, <end_date>)

https://msdn.microsoft.com/en-us/query-bi/dax/calendar-function-dax

or
CALENDARAUTO([fiscal_year_end_month])
https://msdn.microsoft.com/en-us/query-bi/dax/calendarauto-function-dax

 

Your relationships will look like this

 

This gives you a single source for date slicing.

Hope this helps.

jscottNRG's avatar
jscottNRG
Icon for Helper II rankHelper II
8 years ago

edhansAree  thanks again for your suggestions.

 

I created a Date Table and marked it as such on the Modeling tab:

 Calendar (Date Table)

 I tried to set up relationships between Calendar and RoughInspections and FinalInspections -- the first one went fine but I got an error on the second:

 

Relationship activation

You can't create a direct active relationship between RoughInspections and Calendar because that would introduce ambiguity between tables Calendar and Job_info...

 

I'm guessing that is causing this problem, that all records are included when the slicer is 'wide-open,' but when I restrict the range even by one day then the visuals go blank:

 

7/1/2016 - 6/30/20187/2/2016 - 6/30/2018

 

Any thoughts on what I've done wrong?

  • Aree's avatar
    Aree
    Icon for Resolver I rankResolver I
    8 years ago

    jscottNRG 

    Hey Mate,

     

    Can you upload a screen shot of the relationship diagram?

    Also the calendar table becomes the focal point for all timephased data (all sources that have transactions by day).
    Do not link other none timephased data to the calendar table. I believe this is what might be causing your ambiguity but I would need to see the relationship diagram to be certain. 

    • jscottNRG's avatar
      jscottNRG
      Icon for Helper II rankHelper II
      8 years ago

      Aree

      Sorry, I meant to include this in my reply yesterday.  Here is a screenshot of that part of my relationship diagram.  You can see the succesful relationship between Calendar and FinalInspections on the left but the relationship between Calendar and RoughInspections failed.

       

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        8 years ago

        What is the relationship between Job_info and RoughInspections?

        There is some other relationship in your model that is causing the Calendar to RoughInspections to be inactive.

         

        You can, of course, still use it. You just need to use the USERELATIONSHIPS() function in a filter, but that shouldn't be necessary in this case. usually it is when you have 2 or more relationships between 2 tables and you need to swich which realtionship. See this article for more info on that, but I'd still be interested to know what other relationships you have set up are causing the one you tried to go inactive.

         

        Here is an article on how you can create ambiguous relationships that will cause a relationship to go inactive like that as well.