Could someone help me in creating relationship between two tables(one table has compositekey)?

[Anonymous]

Here is my sample data table 1

 EventID   EffectiveDate   ExpirationDate    EventName

1	1/1/2010	12/30/2010	Production
2	1/1/2011	12/30/2011	Injection
1	1/1/2012	12/30/2012	Downtime
3	1/1/2013	12/30/2013	Deferement
1	1/1/2014	12/30/2014	LossProduction
4	1/1/2015	12/30/2015	Constraint
5	1/1/2016	12/30/2016	Shut-In

Table 2 

EventID       ChildID     ChildDate          ChildName

1	A	5/5/2010	OIL
1	B	4/4/2014	GAS
2	C	3/2/2011	WATER
3	D	2/2/2013	CO2
4	E	3/3/2015	O2
5	F	4/4/2016	N2

Expected result in the  below format 

EventID       EffectiveDate    ChildDate         ChildID        ChildName

1	1/1/2010	5/5/2010	A	OIL
2	1/1/2011	3/2/2011	C	WATER
1	1/1/2012
3	1/1/2013	2/2/2013	D	CO2
1	1/1/2014	4/4/2014	B	GAS
4	1/1/2015	3/3/2015	E	O2
5	1/1/2016	4/4/2016	F	N2

In SQL server i can create relationship like 

from Table 1 Join Table2

ON table1.EventID=table2.EventID AND table2.ChildDate BETWEEN table1.EffectiveDate AND Table1.ExpirationDate.

Could anyone help me to create the relationships between theese two tables in Power BI.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

You could create a calculated table.

Result table = 
SELECTCOLUMNS (
    GENERATEALL (
        table1,
        VAR Table1ID = table1[EventID]
        VAR Table1EffectiveDate = table1[EffectiveDate]
        VAR Table1ExpirationDate = table1[ExpriationDate]
        RETURN
            SELECTCOLUMNS (
                FILTER (
                    table2,
                    table2[EventID] = table1[EventID]
                        && table2[ChildDate] >= Table1EffectiveDate
                        && table2[ChildDate] <= Table1ExpirationDate
                ),
                "ChildID", table2[ChildID],
                "ChildName", table2[ChildName],
                "ChildDate", table2[ChildDate]
            )
    ),
    "EventID", [EventID],
    "EffectiveDate", [EffectiveDate],
    "ChildDate", [ChildDate],
    "ChildID", [ChildID],
    "ChildName", [ChildName]
)

Best regards,

Yuliana Gu

[Anonymous]

I appriciate your response Yuliana Gu @v-yulgu-msft,.

Thanks for your query!

I have 12 other tables with same criteria, do i have to join all of them? or can i use Filter in Relationships pane(when creating the relationships)? 

Appriociate your feedback

@Anonymous 

[v-yulgu-msft]

Hi @Anonymous ,

I have 12 other tables with same criteria, do i have to join all of them?

I'm afraid you may have to do that.

or can i use Filter in Relationships pane(when creating the relationships)? 

In such a scenario, it is not possible.

Regards,

Yuliana Gu