Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @sriramn84 ,
Believe that the best option is to create a star schema format. You need to create the following tables:
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |