cancel
Showing results for
Did you mean:
Regular 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

## 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
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: | |
Regular 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

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

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

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

Highlighted
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

## 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: | |

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 113 members 1,638 guests
Recent signins: