Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Creating Relationships between Date Columns - 2 Data Tables and a Common Table for filtering

Hi   Hoping someone can help as this has been bugging me for hours on end now.   I have two tables, one with Orders and one with Quotes. Both tables have a date column for there estimated close d...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

I am not very clear about your problem. Did you add date field from common date table into visual? Please provide some dummy data to describe your scenario. How to Get Your Question Answered Quickly

 

Regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi v-yulgu-msft

     

    Please see some dummy Data below; 

     

     

     

     

     

     

     

     

     

     

     

     

     

    So I have one table 'Sales1' With the Expected Closing column, along with table 2's 'Sales2'[ExpectedClosing] 

     

    Then I have an automatically generated table that just contains dates from 01/01/2000 to 12/31/2019. 

     

    Then I built a relationship from Sales1's dates and Sales2 dates to the Date column within 'CommonTable' 

     

    Then I have visuals which are using both Sales1 and sales2 data and want the single filter using the 'CommonTable'[Dates] Column to be able to limit the visual results affecting both tables with a single filter. 

     

    The relationship works in a fashion but only seems to be linking the dates as the same intermittently. 

     

    Hoping that's clear and not too rambly. 

     

    Thanks in advance.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi, 

       

      If it helps please see below the relationship table, settings and the code I am using for the Date_Filter table (common date table for the filter) 

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      The code I am using for the Date_Filter table is; 

       

      Date_Filter =
      ADDCOLUMNS (
      CALENDAR (DATE(2000,1,1), DATE(2018,31,9)),
      "DateAsInteger", FORMAT ( [Date], "MM/DD/YYYY" ),
      "Year", YEAR ( [Date] ),
      "Monthnumber", FORMAT ( [Date], "MM" ),
      "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
      "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
      "MonthNameShort", FORMAT ( [Date], "mmm" ),
      "MonthNameLong", FORMAT ( [Date], "mmmm" ),
      "DayOfWeekNumber", WEEKDAY ( [Date] ),
      "DayOfWeek", FORMAT ( [Date], "dddd" ),
      "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
      "Quarter", "Q" & FORMAT ( [Date], "Q" ),
      "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
      )

       

      Then the relationship is with the "Date" column. 

       

      Then in PowerBI itself, I have made sure all three date columns are formatted and categorised in the same manner; 

      Data Type: Date/Time 

      Format: MM/DD/YYYY

       

      Any advise would be freatly appreciated. 

       

      Thanks again

       

       

       

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      8 years ago

      Hi Anonymous,

       

      When you add data fields from two tables into two separate table visuals, you can use a slicer date slicer from 'CommonTable' to filter both visuals. It works. But, if you want to combine data from two tables into a single visual, you should drag the date from commontable into table visual rather than date fields from source table.

       

      Regards,

      Yuliana Gu

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi, any solution about this? I have the same problem.