Forum Discussion

Majidbhatti's avatar
Majidbhatti
Advocate IV
6 years ago
Solved

Relationships not detected

Ok this one is weird. I have 4 tables as below diagram. 

Events: Online Excel sheet holds the name of the events

DataTable: is a date table

Reservations: SQL server - Are details of customers reservations

Property Master: SQL server - is table of all locations

 

 

This is how Events table looks like which is linked with Data table

Below is the problem, DATE is from datetable, EVENT NAME is from Events table. NIGHTS is measure SUM of nights coming from Reservations table and looks like below

Nights = If( Sum(Reservations[Nights]) = 0, BLANK(), Sum(Reservations[Nights]) )

 

I am using Matrix visual and as soon as i add the NIGHTS measure in the values place, my event calendar starts repeating itself.

 

Ideally it should pull only the Event name and show no. of nights. What is wrong here?

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , Majidbhatti 

    If I understand Greg_Deckler correctly , you don't need to  remove  any relationship.

     

    What you need is to change  cross filter direction of the relationship between Table 'Events‘ and ’DataTable‘ to  'Both'. 

    And then you need to make relationship between  the Property Master and  Reservations  inactive(or make relationship between  DataTable and  Reservations  inactive)

     

    Best Regards,
    Community Support Team _ Eason

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    The core of the problem here looks to be your relationships. Events is not filtering Reservations. You could fix this by making your relationship between Events and PropertyMaster Both for example but this would likely cause a relationship to go inactive. I would create a relationship between Events and Reservations and remove the relationship between DateTable and Reservation.

    • Majidbhatti's avatar
      Majidbhatti
      Advocate IV
      6 years ago

      Hi Greg_Deckler 

       

      I donot see any link between Events & Reservations as events are calendar per property and reservations are guest bookings. Similarly, cannot remove link between reservations & datetable as the datetable is the main date table of all other tables in the model.

       

      Can you think of any other solutions?

       

      Regards

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        6 years ago

        Hi , Majidbhatti 

        If I understand Greg_Deckler correctly , you don't need to  remove  any relationship.

         

        What you need is to change  cross filter direction of the relationship between Table 'Events‘ and ’DataTable‘ to  'Both'. 

        And then you need to make relationship between  the Property Master and  Reservations  inactive(or make relationship between  DataTable and  Reservations  inactive)

         

        Best Regards,
        Community Support Team _ Eason