How to handle time range in the relationship

[CornelisV]

Dear all,

I want to connect 3 tables but suffer from time range from one table, that can only linked with column 'ID'.

Table Date is generated from Autocalendar.

Table Range has a Start and End time with corresponding ID.

Table Score has the columns ID and the Score.

Goal is to combine three tables in in a Table view like this:

A relationship between ID is possible, but the relationship between date and date range (start and end) is not possible. I can only choose Start or End dates to build a relationship, but that will result in an incomplete table.

Do you have a smart DAX solution?

Best regards,

Cornelis

[CornelisV]

Dear @MasonMA ,

That is a massive solution, easy to create and avoiding memory use in Power BI. I fully agree with @v-menakakota that this is an interesting topic and a good learning point.

[v-menakakota]

Hi @CornelisV ,

Thank you for the update.

[Ashish_Mathur]

Hi,

In Power Query, you can create another column with each cell showing each date which falls within a range.  This way each row of start and end date will explode into multiple rows.  The you can create a relationship with the Calendar table.  This is broadly how it is done in Power Query

={Number.From(start)..Number.From(End)}

[CornelisV]

Hi @Ashish_Mathur ,

Thank you for your support. Yes, with Power Query , you can multiple observation in table "Range". That is more a Query than a DAX solution. Creating multiple rows from a time range has been advised earlier in this forum.

Best regards,

Cornelis

[v-menakakota]

Hi @CornelisV 
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @MasonMA   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team  

[MasonMA · Accepted solution]

Hi, 

With DAX, i'd suggest building a Bridge table and have it connected with Score Table. 

Bridge =

VAR _Expanded =

    ADDCOLUMNS (

        GENERATE (

            'Range',

            VAR StartDate = 'Range'[StartDate]

            VAR EndDate = 'Range'[EndDate]

            RETURN

                CALENDAR ( StartDate, EndDate )

        ),

        "DateTable", [Date]

    )

RETURN

    SELECTCOLUMNS ( _Expanded, "Date", [Date], "ID", 'Range'[ID] )