Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create relationship between two table with start and end date

I have two table first table is about ID and Start and End Time that Value Change based on Time second table is ID and Date. I want to get the value from first table in second table by mapping ID an...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    One of ways to achieve this is to create a datamodel like below.

    Please check the below picture and the attached sample pbix file.

    It is for creating a new column in the Value Target table.

     

     

     

     

     

    Value CC =
VAR _id =
    RELATED ( 'ID'[ID] )
RETURN
    MAXX (
        FILTER (
            Data,
            Data[ID] = _id
                && Data[Start Date] <= 'Value Target'[Date]
                && Data[End Date] >= 'Value Target'[Date]
        ),
        Data[Value]
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

One of ways to achieve this is to create a datamodel like below.

Please check the below picture and the attached sample pbix file.

It is for creating a new column in the Value Target table.

 

 

 

 

 

Value CC =
VAR _id =
    RELATED ( 'ID'[ID] )
RETURN
    MAXX (
        FILTER (
            Data,
            Data[ID] = _id
                && Data[Start Date] <= 'Value Target'[Date]
                && Data[End Date] >= 'Value Target'[Date]
        ),
        Data[Value]
    )