Many to Many Relationship with Dates and Ids

[sriramn84]

Hello All, I have the following 2 tables.

DriverLog Table:

BusId	CreatedDate	Trip	Loc	Lat 	Long	PassengerCount
1	2/07/2019	T1	A	10	20	5
1	2/07/2019	T1	A	11	22	6
2	3/07/2019	T1	A	11	45	5
2	3/07/2019	T2	B	13	12	2
2	3/07/2019	T1	B	14	67	2
1	10/07/2019	T1	C	14	99	3
1	10/07/2019	T2	A	12	10	5

AutoPosition Table:

BusId	CreatedDate	Loc	Lat 	Long
1	2/07/2019	A	10	20
1	2/07/2019	A	11	22
2	3/07/2019	A	11	45
4	3/07/2019	C	13	12
2	3/07/2019	B	14	67
1	10/07/2019	C	14	99
1	10/07/2019	A	12	10

Purpose : Now we need to verify if the DriverLogs are real.

- Can slice the DriverLog Table based on BusId/Trip/Date and get the Location value from "Loc" column

- No RelationShip between the tables can be created using the Id Column

- Plot the Latitude and Longitude of the AutoPosition table in a map

Tried the following :

- Created a Many-2-Many Relationship between the two tables based on "Loc" column

- Created filters for Date, Id and Trip & this results in a Location list(based on Loc column)- all from DriverLogTable

- Plotted the Lat & Long from AutoPosition Table in a Map

Issue :

The Map is only filtered by Location. But I want it to be filtered by the same Id, Date, Trip and Loc as selected in the dropdowns. Can someone help in achieving this.

Thanks in advance.

[MFelix]

Hi @sriramn84 ,

Believe that the best option is to create a star schema format. You need to create the following tables:

• Location
• Bus
• Lat Long

All of these tables should have unique values for each of the columns, regarding the Lat Long you must also add an ID column that is composed of the values of Lat and Long concatenated and make a similar column on the other two tables.

If needed can also be added a calendar table for the dates.

Then make a one to many relationships between the 3 previous tables and your DrivrsLog and Autoposition.

To make your visuals you must use the values from these dimension tables to have everything working.

Check the PBIX file attach, as a first approach, please get back to me with further insights on what you need to achieve so that I can help you achieve the best approach.

Regards,

MFelix

[sriramn84]

Hi @MFelix , Thank you very much for the response and the effort to try this in a sample.

I tried to implement the dimension table, Now I am able to filter the Ids and Locations.

The next issue I am facing here is with the Timestamps. There is a dateslicer based on driverlog, the autoposition map is plotted with Lat,Long and Timestamp.

- When I try to create a dimension with the datetimestamp, then there is only one point plotted on the Map.

- If I create a date column on both the dimension and the autoposition table, then I cant populate a map with just the  date as it required a timestamp.

How to filter the dates, thanks a lot in advance.

[MFelix · Accepted solution]

Hi @sriramn84 ,

You need to create another dimension table with the calendar and related it to the DriveLog and AutoPosition table, this will make you have 4 dimension tables instead of 3.

Regards,

MFelix