Using a inactive relationship in selectcolumn dax

[Nudo]

Hey

I've created a Date Table from multiple different tables createdates that looks like this

Date Table = DISTINCT(UNION (SELECTCOLUMNS ( 'Lead', "Date", 'Lead'[CreatedDate] ),SELECTCOLUMNS ( 'Opportunity', "Date", Opportunity[CreatedDate] ),SELECTCOLUMNS ( 'Lead Instance', "Date", 'Lead Instance'[CreatedDate] ),SELECTCOLUMNS(Event,"Date",Event[CreatedDate])))

Then I create a relationship to the date table from those columns. I've noticed though these only work if the relationship is active, just wondering how can I use this and use inactive relationships. I know how I can work around this problem but I'm also curious as to if there's a solution.

[v-jingzhang]

Hi @Nudo

I recommend to use CALENDAR function to create a Date table. Its syntax is 

CALENDAR(<start_date>, <end_date>) 

You just need to give an early enough date for start_date and a late enough date for end_date then it will generate a date table which contains all dates between above two dates. Ensure that its date range is wide enough to cover all dates in your four tables. 

Date Table = CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2022, 12, 31 ) )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Nudo]

The problem I've found using calendar is are using date times and it's only checking for date time at 12:00 and won't look at the whole day. Also I don't think that resolves my original question.

[v-jingzhang]

Hi @Nudo 

Yes, calendar function returns datetime values with timestamp 12:00 a.m. but you can change the Date column from Date/time data type to Date type to remove timestamps. 

Currently you are selecting CreatedDate columns from multiple tables to create a calendar table. What relationships exist between tables? What effect or output result do you want to get through those inactive relationships? Can you provide some mock data to show the expected result you want? 

Jing