Forum Discussion

Dayna's avatar
Dayna
Icon for Helper V rankHelper V
7 years ago
Solved

Cyclic relationship on table structure

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

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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

     

6 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

    • Dayna's avatar
      Dayna
      Icon for Helper V rankHelper V
      7 years ago

      Hello,

       

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

       

      Many thanks,

      Dayna

       

       

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Dayna
      Icon for Helper V rankHelper V
      7 years ago

      Hi Jimmy,

       

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

      • v-yuta-msft's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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