Issue to calculate the distance covered by a vehicle based on the reservation date and date of use

[MiloDi]

Hello dear Community, 

I have 2 files containing car plates and other information.
A reservation file that includes the car's license plate, departure date and other information I don't need.
A usage file with the car's license plate, dates of use and distance traveled.
I can't link the tables together directly, as the relationship is many-to-many.
I'd like a table where I can see the car's license plate, the booking dates and if there's an entry for the date of use, I'd like to see the 2 dates and the distance on the same line. I've given you an example of what's expected.
So I created a bridge table.
I've tried a combined table and measurements, but I'm not getting what I need.

Link Documents : https://we.tl/t-HQagERImRK

thank you in advance

[MiloDi]

Many thanks for your help
I've also tried this solution, removing the 2 tables, but I don't get the expected result. I've tried several formulas, but at the point where I have to display the date of use, nothing works, because the aim is to show the date of reservation and the date of travel, as well as the distance, in order to show the vehicles that are really used during the period requested.
In your example, this doesn't work either. It returns distances for GHI but it doesn't have any.
I've done this with my data, but every time I get the same distance as in the example, but with more columns that I don't use and more plates.

[girishthimmaiah]

It appears that you're attempting to merge reservation and usage data for vehicles, aligning them by the license plate and date. Because there is a many-to-many relationship, a bridge table is an appropriate solution.

Solution Approach in Power BI:
Create a Date Bridge Table:

Create a unique list of dates that span both reservation and usage.
This will serve as a common reference for both tables.
Create Relationships:

Connect the reservation table to the bridge table by using the "Date départ".
Connect the usage table to the bridge table by using the "Date utilisation".
Connect both tables to the bridge table by "Plaque" (car plate).
Create the Table in Power BI:

Take the Date Bridge table as the primary axis.
Insert "Plaque" from the reservation table.
Insert "Date départ" from the reservation table.
Insert "Date utilisation" and "Distance" from the usage table.
DAX Measure for Distance: If the direct relationship does not work, build a measure:

DAX

Distance_Used =
CALCULATE(
SUM(Usage[Distance]),
CROSSFILTER(Usage[Date utilisation], DateBridge[Date], BOTH)
)

[MiloDi]

Hello @girishthimmaiah 

Thank you for your reply but 

Either I haven't understood what you're trying to do, or I can't do what you're trying to do:

I've created 2 bridge tables. One for the date and one for the plates. Here are the scripts.

Dates_Combinees = DISTINCT(UNION(SELECTCOLUMNS('DimRéservations', "Date", 'DimRéservations'[Date départ]), SELECTCOLUMNS(DimUtilisationVoiture, "Date", DimUtilisationVoiture[Date])))

Plaques_Combinees = DISTINCT(UNION(SELECTCOLUMNS('DimRéservations', "Plaques", 'DimRéservations'[Plaque Extraite]), SELECTCOLUMNS(DimUtilisationVoiture, "Plaques", DimUtilisationVoiture[Véhicule])))

when I try to join the table plaque combinees with the second table, the relation is inactive. 

However, when I try to link them together, I get this type of error message.

Ambigu path between.... 
Can you tell me where I went wrong?
Thanks

[v-zhouwen-msft]

Hi @MiloDi ,

Please check which of the attached scenarios meets your needs and correct me if I have misunderstood.

Best Regards

[MiloDi]

Hello @v-zhouwen-msft 

Sorry for the late answer. 

Thank you for the reply. 

That's about it.
On the sample it seems to work perfectly. But when we use measure 3 and 4, we see all the dates from for the reservation.
In case number 1 with measure and measure 1 it's almost like that. We only see the booking dates and the distance over several routes.
But with case number 2, when I aggregate the distance, I see a line for all the car plates with the booking dates, even if the vehicle hasn't been booked.

[v-zhouwen-msft]

Hi @MiloDi ,

Would it be possible to provide the .pbix file without the sensitive data and state what the expected results are?

If you are unsure how to upload data please refer to

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards

[MiloDi]

Hello I send you private message. 

I'm encountering the following problem, even with your modifications sent by e-mail.
I've created the tables and I don't have any problems with multiple tables.
I put in a table, the plates of the combined table, the starting date of the Reservations table. So far so good, I've got one or two reservation dates.
I add your measurement3 :
Measure 3 =
VAR _date = SELECTEDVALUE('Reservations'[Departure date2])
VAR _result = FILTER(VALUES('Travel'[Date]),[Date] = _date)
RETURN IF(ISBLANK(_result),” ”,_result)
And then it gives me one line per plate, per day, for all the plates.
Otherwise, I just put in measure 4, and it works.
I do have the license plate, the reservation date and the mileage, but I don't have the days on which he used the car.

If the date table has 90 rows and 100 plates, then I have 9000 rows.

Thank you 

Link : https://we.tl/t-oACaI6Rk23

[v-zhouwen-msft]

Hi @MiloDi ,

I don't think the bridging table is very useful, please remove it.

Since these two fields come from different tables, and I have replaced blank with an empty string in 'Measure3', it results in a Cartesian product.

[MiloDi · Accepted solution]

I found the solution.
I did it all in Power Query.
The problem was that I had to show not only the date of use but also the date of reservation. With the different measures, without adding the reservation column, I had everything.
But when I added the column, I had all the reservation dates for each plate.
couldn't answer me with any certainty, because he only had a sample of data, and special cases weren't included.
I added an ID for reservations, and if the reservation existed in the utilization table (in the same time slot, same plate, user) then it had a positive id, otherwise a negative id.
Many vehicles had been driven without a reservation or with several drivers.
I didn't make any bridge tables.
Many thanks to @v-zhouwen-msft  who put me on the path.

File is below