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

[Anonymous]

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 date and I want a single filter to apply to the values for a combined Orders and Quotes.

I have created a date table and have dates ranging from 01/01/2000 to 31/12/2025 in the "Common Table" which I want to use as the filter column.

The date table was created fine and I have formatted all date columns the same. (Date - M/D/YYYY).

I then went to the relationship builder and created a relationship between all 3 date columns.

Then when I change the filter to use the newly created "Common" Date column with should apply and filter both data tables it is showing way under what I would expect when I use both Date columns from the original sources to filter their respective values.

----

As a quick test and so show my thoughts visually I created a table that has the order details, original estimated close date and then the estimated close date from the common table and it is only associating with the occasional date.

If none were associated I would understand I had done something wrong, but the odd date is tying together so I've no idea whats going wrong with the relationship.

Any help would be greatly appreciated as really wanted some visuals using both data sources simultaneously, to work with the single filter. 

Thanks 

[v-yulgu-msft]

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]

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.

[v-yulgu-msft]

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

[frantzgonzales]

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

[Anonymous]

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

[Anonymous]

Also, as a further test, I deleted one relationship so it was between a single data source and the newly created common table and i see the exact same associations. 

My thinking was all three tables needed the same date to show but even just as a 1:1 table relationship it still fails to fully replicate the same results as the original date column so the original data source. 

Thanks,