cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rajesh1 Frequent Visitor
Frequent Visitor

Graph not corresponding to date slicer

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
 

7 REPLIES 7
Community Support Team
Community Support Team

Re: Graph not corresponding to date slicer

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Rajesh1 Frequent Visitor
Frequent Visitor

Re: Graph not corresponding to date slicer

hi @v-shex-msft Xiaoxin Sheng

 

Thanks very much for taking your time to assist.

Let me try this and get back. 

 

Regards,

Rajesh

Super User
Super User

Re: Graph not corresponding to date slicer

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 Frequent Visitor
Frequent Visitor

Re: Graph not corresponding to date slicer

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

 

 

Highlighted
Rajesh1 Frequent Visitor
Frequent Visitor

Re: Graph not corresponding to date slicer

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. :-(

Super User
Super User

Re: Graph not corresponding to date slicer

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

Community Support Team
Community Support Team

Re: Graph not corresponding to date slicer

Hi @Rajesh1,

 

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

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |