Cyclic relationship on table structure

[Dayna]

Hi All,

I have two database tables:

Server and Test Log

In the server table, I store some basic information about the server, for example, the date it was issued, the name, etc...

In the test log table, I store information related to tests on the server. This also has the ServerID as a foreign key back to the server table. So far, so good.

In PowerBI, I want a report that will plot the dates of the server commissioning and the tests on one chart, so I need to refer to a calendar. 

The server date is not the same date as the test date, so I can't link to one, then jump to the other, as the information is incorrect.

Linking both to the same calendar causes a cyclic relationship.. 

How is best to overcome this issue, please? 

Many thanks,

Dayna

[v-yuta-msft]

Hi Dayna,

Change the direction between 'MasterCalendar' and 'DR TestingLog', between 'DR Server' and 'DR TestingLog' from bidirection to single direction.

Regards,

Jimmy Tao

[Dayna]

Hi Jimmy,

Tried it - but still unable to make the relationship active. I've included a screenshot of the updated ERD:

[v-yuta-msft · Accepted solution]

Hi Dayna,

Sorry for late, I have reproduced your issue like below. For example, if you create a slicer based on one column in MasterCalendar table and select a value to filter DR TestingLog. There're two kinds of filter route, one is MasterCalendar-> DR TestingLog, another is MasterCalendar-> DR Server-> DR TestingLog. So there may be two different filter results in DR TestingLog which may cause ambiguity. 

  

Regards,

Jimmy Tao

[Dayna]

Hi Jimmy,

That makes sense, and thank you for your help. It's much appreciated!

All the best,

Dayna

[Stachu]

I'd try with changing the relationships from biderectional to single direction

[Dayna]

Hello,

I've just tried and it's made no difference sadly, the error is attached.

Many thanks,

Dayna