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.
We have the following 3 tables:
Vehicles
=========
VehicleID
VehicleNumber
Movement
========
VehicleID
Date
Location_Latitude
Location_Longitude
Function
SpeedCons
=========
VehicleID
Date
Speed
Consumption
Vehicles is related to Movement by VehicleID (many to one & cross filter direction "both")
Vehicles is related to SpeedCons by VehicleID (many to one & cross filter direction "both")
Vehicles.VehicleNumber is used in a multi-selection slicer
Movement.Date is used in a Date range slicer
When a vehicle number is chosen, the appropriate Vehicle Movements are presented from 'Movement' table in a map.
The speed and consumption graph shows the relevant vehicles.
I am unable to filter the SpeedCons Date in accordance with the Date range slicer since an active relationship between
SpeedCons table and Vehicles table on VehicleID already exists.
Please can someone guide me on how to ensure that the Speed Consumption graph also behaves as per the Date Range slicer.
Thank you,
Rajesh
Hi,
Create a Calendar Table with running dates. Create a relationship from the Date column of the Movement Table to the Date column of the Calendar Table. Create another relationship from the Date column of the SpeedCons Table to the Date column of the Calendar Table. Create the Date slicer from the Calendar Table.
Thanks for your time @Ashish_Mathur.
In this case, I need to show the speed and consumption of a vehicle chosen from Vehicle table. But, theses figures should pertain to a chosen date range from the Movement table.
Hence, here the speedcons table requires 2 relations (Viz.) one on VehicleId to the Vehicle table and one on the date selection from the Movement table.
If I create a calendar table and relate to the Movement table and/or SpeedCons, then Power BI disallows the second relation Movement table's date to SpeedCons table's date.
This is what the difficulty is. 😞
I do not understand your question. Show your datasets and the expected result.
HI @Rajesh1,
Current relationship is based on vehicle id, but it seems like you want to use use both vehicles id and date field to link these tables. I think you need to add bridge table to rebuild relationships.
Below are sample formulas:
Calculate Table formula: Bridge = VAR _vehicleList = DISTINCT ( UNION ( VALUES ( Vehicles[VehicleID] ), VALUES ( Movement[VehicleID] ), VALUES ( SpeedCons[VehicleID] ) ) ) VAR _datelist = UNION ( VALUES ( Movement[Date] ), VALUES ( SpeedCons[Date] ) ) VAR _calendar = CALENDAR ( MINX ( _datelist, [Date] ), MAXX ( _datelist, [Date] ) ) RETURN ADDCOLUMNS ( CROSSJOIN ( _vehicleList, _calendar ), "V_Date", [VehicleID] & "-"& [Date] ) Calculate column formula: V_Date= [VehicleID] & "-"& [Date]
Steps:
1. Create new table as bridge table based on above formula.
2. Add calculated column 'V_Date' to 'Movement' and 'Speed Cons' tables. (based on 'VehicleID' and 'Date')
Relationships:
Vehicles -> Bridge Table (Vehicles[VehicleID] to 'Bridge Table'[VehicleID])
Movement -> Bridge Table (Movement[V_Date] to 'Bridge Table'[V_Date])
SpeedCons -> Bridge Table (SpeedCons[V_Date] to 'Bridge Table'[V_Date])
Regards,
Xiaoxin Sheng
hi @v-shex-msft,
I created the table verbatim as per your formula.
I then deleted the existing relationships to create the ones that you suggested.
Vehicles -> Bridge Table (Vehicles[VehicleID] to 'Bridge Table'[VehicleID])
I am getting the following error:
Create relationship
A circular dependency was detected: BridgeTable[BridgeTable-7789709...], BridgeTable[VehicleId], BridgetTable[876098...].
Where am I going wrong??
Hi @Rajesh1,
Can you please share a pbix file with some sample data so that we can test on it?
Regards,
Xiaoxin Sheng
hi @v-shex-msft Xiaoxin Sheng
Thanks very much for taking your time to assist.
Let me try this and get back.
Regards,
Rajesh
Covering 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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |