Graph not corresponding to date slicer

[Rajesh1]

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
 

[Ashish_Mathur]

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.

[Rajesh1]

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. 😞

[Ashish_Mathur]

I do not understand your question.  Show your datasets and the expected result.

[v-shex-msft]

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

[Rajesh1]

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??

[v-shex-msft]

Hi @Rajesh1,

Can you please share a pbix file with some sample data so that we can test on it?

Regards,
Xiaoxin Sheng

[Rajesh1]

hi @v-shex-msft Xiaoxin Sheng

Thanks very much for taking your time to assist.

Let me try this and get back. 

Regards,

Rajesh