Find a date between 2 tables based on a range of dates

[Takama]

Hi,

First of all, I'm a new user of Power BI and this is my 1st post 

I would like to create a relationship between 2 tables but I don't know how to.

1st table is set like :

Car ID - From(date) - To(date) - Driver ID

2nd table is set like :

Car ID - Date of use

I would like to create a link between these 2 tables so I can see on the 2nd table who was the driver at a specific date.

I've searched on several topics and the solution seems to link these tables via a Calendar table, that's what I did.

So I have all dates from the 2 tables link to the Calendar table (with 1 link inactive for the 1st table) but I don't know the formula I have to use in the 2nd table to retrieve the Driver ID from the 1st table.

Thanks in advance for your help ! 🙂

[Abduvali]

@Takama,

I think this could help as its already identifies dates between 2 dates just expand on that by adding relevant fields:

• See PBIX in my library: Calculate dates between two measures

Hope it helps.

Regards

Abduvali

[Takama]

Hi @Abduvali,

I will check your link when I will be registered.

@Ashish_Mathur,

I understood the filter part of your formula. Just the EARLIER function is not clear for the moment.

I'm searching 🙂

I may again come back to you as atualy, my tables are more complex than that and I must adapt your formula. I will let you know if I am blocked again.

Thank you

[Ashish_Mathur]

You are welcome @Takama

[pxg08680]

@Takama

Make a relationship between the two tables on CarId.

Then in second table do this

Column = IF(Table5[Carid] = RELATED(Table6[Carid]),RELATED(Table6[Driverid]))

Below image shows Table1 data, Table2 data and extra calculated column with driver id.

[Takama]

The problem is that Car ID is not unique in both tables, so I can't link them.

Here are examples of what look the tables.

Table 1 :

Car 1 - 01/01/2017 - 15/03/2017 - User 1

Car 1 - 01/06/2017 - 22/09/2017 - User 2

Car 2 - 15/02/2015 - 18/10/2017 - User 3

Car 2 - 19/10/2017 - 02/11/2017 - User 1

Table 2 :

Car 1 - 01/03/2017 (I want then to find User 1)

Car 2 - 01/03/2017 (I want then to find User 3)

Car 2 - 01/11/2017 (I want then to find User 1)

[Ashish_Mathur · Accepted solution]

Hi @Takama,

Try this calculated column formula in Table2

=CALCULATE(CONCATENATEX(Table1,Table1[Driver ID]),FILTER(Table1,Table1[Car ID]=EARLIER(table2[Car ID])&&Table1[From]<=EARLIER(table2[Date])&&Table1[To]>=EARLIER([Date])))

Hope this helps.

[Takama]

---

@Ashish_Mathur wrote:

Hi @Takama,

Try this calculated column formula in Table2

=CALCULATE(CONCATENATEX(Table1,Table1[Driver ID]),FILTER(Table1,Table1[Car ID]=EARLIER(table2[Car ID])&&Table1[From]<=EARLIER(table2[Date])&&Table1[To]>=EARLIER([Date])))

Hope this helps.

---

Hi Ashish_Mathur,

Unless mistaken, you can not refer to an other table if they are not linked.

[Ashish_Mathur]

Hi @Takama,

My solution will work without a link.  Please try it.

[Takama]

Hi Ashish_Mathur,

You are right, we can access the data from other tables even if they are not linked.

I assumed it was not possible because when I was writing the formula, the tables were not automaticaly proposed.

As I said, I'm a new user of Power Bi and I'm trying to understand your formula :

=CALCULATE(CONCATENATEX(Table1,Table1[Driver ID]),FILTER(Table1,Table1[Car ID]=EARLIER(table2[Car ID])&&Table1[From]<=EARLIER(table2[Date])&&Table1[To]>=EARLIER([Date])))

CONCATENATEX(Table1,Table1[Driver ID]) : is it to distinct all the Driver ID ?

Table1[Car ID]=EARLIER(table2[Car ID])&&Table1[From]<=EARLIER(table2[Date])&&Table1[To]>=EARLIER([Date])) :

I don't understand how this filter works. Could you explain it ?

[Anonymous]

You could join these two tables using a new table with only Car Id's in it and then apply the formula suggested by @Ashish_Mathur.

[Takama]

---

@Anonymous wrote:

You could join these two tables using a new table with only Car Id's in it and then apply the formula suggested by @Ashish_Mathur.

---

I think I can't access tables through other tables.

[v-huizhn-msft]

Hi @Takama,

Whay you have no access for another table. You can create a new table as Table3 in the screenshot above by clicking "New Table" under Modeling on Home page, then type formula below.

Table3 =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "Car id", Table1[Car id] ),
        SELECTCOLUMNS ( Table2, "Car id", Table2[Car id] )
    )
)

Best Regards,
Angelia

[Takama]

---

@v-huizhn-msft wrote:

Hi @Takama,

Whay you have no access for another table. You can create a new table as Table3 in the screenshot above by clicking "New Table" under Modeling on Home page, then type formula below.

Table3 =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "Car id", Table1[Car id] ),
        SELECTCOLUMNS ( Table2, "Car id", Table2[Car id] )
    )
)

Best Regards,
Angelia

---

Hi Angelia,

My concern is not to create the table 3.

The issue is to create a formula in table 1 to access data in table 2. This would not work via table 3.

[Anonymous]

I guess, you are expecting the same result which is derived from the data (as example) you shared.

[Takama]

So the result would be in table 3 ?

[v-huizhn-msft]

Hi @Takama,

For the sample table you posted, Table2 Car 2 - 01/11/2017 (I want then to find User 1), the 01/11/2017 is between 15/02/2015 - 18/10/2017 in table1, you want to get User1, why not User3? 

Best Regargs,
Angelia

[Takama]

@v-huizhn-msft wrote:

Hi @Takama,

For the sample table you posted, Table2 Car 2 - 01/11/2017 (I want then to find User 1), the 01/11/2017 is between 15/02/2015 - 18/10/2017 in table1, you want to get User1, why not User3? 

Best Regargs,
Angelia

---

Hi v-huizhn-msft,

Because in this example, User 3 used the Car 2.