Relationships not detected

[Majidbhatti]

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?

[Greg_Deckler]

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]

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

[Greg_Deckler]

See @v-easonf-msft 's post. Very difficult to troubleshoot these things without sample data and understanding which fields tables are related on. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Majidbhatti]

Hi @v-easonf-msft , thanks, BOTH did the trick between DATETABLE & EVENTS.

[v-easonf-msft · Accepted solution]

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