Building relationships between two related tables to a third Dates table

[Anonymous]

I have two tables which are related to each other( Incidents and ESRs)

( One incident(SR) can generate one or more ESRs at different times)

The ESRs have a created Date and the Incidents(SR) have a created date.

I have generated a "Dates" table to be able use for analysis across items based on dates

problem is that I can't create an active relationship between the Incidents and Table_Dates as PB is telling me I have ambiguous paths between the tables.

This seems so simple but I am lost..

I want to show on a single date based graph the number of Incidents created and the number ESRs created... 

[Ashish_Mathur]

Hi,

That is indeed an ambiguous relationship.  I think you should delete the relationship between the ESR and Incidents Table.  Create a relatiosnhip from the Table-Dates table to the other 2 tables. 

[Anonymous · Accepted solution]

So I had to re-think the dataset and remove the direct Relationship between ESR and Incidents... created an inactive relationship to use in measures to "count" ESRs generated from Incidents...

I can see based on the "rules" in PowerBI how this is ambigious, but in an ER diagram/DB terms it's not ...  

but this works now... for what I need to do..

Thanks

[DaFloDo]

hello,

if I understand correctly, ESR can not exist without SR, right? 

So you could try to just delete the relationships between Table_Dates and ESRs. 

So you end up with only the following relations:

- Table_Dates (1) : Incidents (many) [Date]

- Incidents (1) : ESRs (many) [IncidentId]

Any filtering on date should be passed through over incidents to the related ESRs.

If you always have at least one ESR for each incident, it might be better to create the date relation between Table_Dates and ESRs instead of incidents - that depends on your needs.

best regards,

florian

[Anonymous]

yes ESRs come from Incidents/SRs but they are "independent" in that we look at ESR creation over time and then analyze ESR metrics.  We don't always look at the Incidents when analyzing the ESRs.. so if there is no relatoinship between ESR and the Dates table we can't create, as an example, a line graph showing the number of ESRs created over time.  unless we used the Created_Date on the ESR.

Incidents are managed by Support and ESRs are managed by Engineering.  An Incident may generate one or more ESRs..

ESRs are not created at the same time as the Incident... we actually measure how many days it takes on average for an ESR to be generated after an incident was created.

Make sense?

On one Graph I want to show a timeline and then show the number of Incidents created and the Number of ESRs created. 

I also created the Table of dates to be able to graph on one timeline Open and Closed ESRs over time.  using the "userelationship" function in a measure for counting the number of closed ESRs. by using the in active relationship