3

Scenerio:

Fact Table: Fact_Travel_Plan

Dimension Table: Dim_City

I have created relationship with Dim_city and Travel Plan as

Dim_City.CityId --> Fact_Travel_Plan.EntryCityId

Dim_City.CityId --> Fact_Travel_Plan.ExitCityId

While establishing the same relationship in powerbi , after the first relationship it is giving an error "A relationship already exits with this table" . Please let me know how to handle this. Thanks

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
th, td {
    padding: 5px;
}
</style>
</head>
<body>

<table style="width:100%">
  <tr>
    <th>Plan</th>
    <th>EntryCityID</th>
    <th>ExitCityID</th>
    <th>NoOfDay</th>
  </tr>
  <tr>
    <td>Plan1</td>
    <td>1</td>
    <td>2</td>
<td>3</td>
  </tr>
  <tr>
    <td>Plan2</td>
    <td>3</td>
    <td>2</td>
<td>2</td>  </tr>
  <tr>
     <td>Plan3</td>
    <td>2</td>
    <td>1</td>
<td>4</td>  </tr>
</table>

</body>
</html>
AJIL D S JINS
  • 51
  • 3

2 Answers2

1

You cannot have two active relationships between two tables (even indirect relationships through other tables). This is because all the relationship is there for is to define default joining behaviour. If you defined two active relationships it wouldn't know how to perform default joining behaviour, so there is no point in having the active relationship in the first place.

If you need it, you could create a roleplaying dimension - basically have two copies of the Dim_City table, and call one Entry City and the other Exit city.

OR, if you don't mind breaking strict dimensional modelling rules, you can pull those relationships into the fact table via calculated columns.

OR, if you don't mind messing around a bit you can join the two role-playing dimensions together, have a composite key (using both EntryCityID and ExitCityId) into a new table with duplicated attributes (e.g. DimCities[EntryCity] and DimCities[ExitCity]

Jeff
  • 12,555
  • 5
  • 33
  • 60
0

Set first relationship as Inactive. And then create second.

Sandip Jagdale
  • 35
  • 8