Forum Discussion

jhorta's avatar
jhorta
Frequent Visitor
8 years ago
Solved

Date Slicer for multiple columns in different tables

I'm new in the BI and Power BI world, I'm creating a PoC in PowerBI. My data sources are all scattered, mainly on spreadsheets, and I'm also pulling data from Google Analytics.   In one of my table...
  • MFelix's avatar
    MFelix
    8 years ago

    Hi jhorta,

     

    You need to create a Calendar table and them make a relationship between the tables that way you canuse the calendar table to make the visuals / slicers you need and all the information will be transform based on that.

     

    On this post you can find several ways to create a calendar but if you google "POWER BI Calendar" you will find lots of different ways to create a calendar table.

     

    Just as a best pratice be sure that the calendar table is starting on 1 January and ending on 31 December (this can be different years)

     

    Regards,

    MFelix

rbwidener's avatar
rbwidener
Frequent Visitor
8 years ago

Hi jhorta, I am having a similar issue and have created a Calendar table, but cannot seem to get the relationships to work. I two data tables from different source systems and I'm trying to create a single date filter (month/year) on my visualization page. The data tables have similar data, like order numbers, created date/time, dispatched date/time, completed date/time, etc. I've checked the data types of all the columns and they seem to be the same. Any ideas on what I'm doing wrong? Any feedback would be greatly appreciated.

 

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

Hi rbwidener,

 

You are trying to make more than 1 connection between date table and the other tables correct?

 

You need to make inactive relationships between the several dates and then create calculated measures using the USERRELATIONSHIP formula to active the relationship upon context.

 

Regards,

MFelix

  • rbwidener's avatar
    rbwidener
    Frequent Visitor
    8 years ago

    Thanks MFelix - I've seen some posts about this and will give it a try!

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      8 years ago

      rbwidener

       

      If you need any help or explanation please say.

       

      Regards,

      MFelix

      • rbwidener's avatar
        rbwidener
        Frequent Visitor
        8 years ago

        Ok, I've created relationships between all of the dates on my two data tables and the calendar data table, then did a USERELATIONSHIP function for the created date on each of the data tables. The tutorial I was reading said that it didn't matter what the calculate function is (https://msdn.microsoft.com/en-us/library/hh230952.aspx) - maybe I listed the columns in reverse... I listed USERRELATIONSHIP('Data Table'[Created Date],'Calendar'[Date]). When I look at my visualization filter, using 'Calendar'[Date], it's blank:

         

        Any ideas? Thanks for all of your help!