Forum Discussion

Nudo's avatar
Nudo
Icon for Helper I rankHelper I
4 years ago

Using a inactive relationship in selectcolumn dax

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.
Need Help

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Nudo
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        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