you can't create a direct active relationship between Survey and Incident

[andrew_hardwick]

I am trying to create a report showing the number of survey responses received based on the number sent. The number sent is stored in the Incident Table and the received in the Survey table. Each table also has a date, which I am using a date table to compare.

The table is working, and shows 4 columns; Date, Surveys Sent, Surveys Received and percentage. I would also like to be able to filter on the country where the survey was sent from (stored as a field in the Incident Table). As I want the sent surveys to be linked to the received surveys, I tried to create a relationship, but as there is already one between dates, I got an error.

Any suggestions how to get around this?

 

[Greg_Deckler]

USERELATIONSHIP

[vanessafvg]

i love how verbose you are @Greg_Deckler 

[vanessafvg · Accepted solution]

there are 2 ways of dealing with role playing relationships (that is what it is when a date from the same date table is being used 2 x but linked to different dates)

1. use  the userelationship function for an inactive relationship (dotted line is an inactive relationship), i.e

=CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(InternetSales[ShippingDate], DateTime[Date]))

https://msdn.microsoft.com/en-us/library/hh230952.aspx

2. Create another date table and use that table to link the 2nd date so that the relationship is active 

[andrew_hardwick]

Thats great, I added a userelationship formula and it worked fine. Thanks.

[Min_02]

Hi @andrew_hardwick , could you please share the measure you created with userelationship? In which table did you created the measure? What did you do after creating the measure?

I have an identic problem, hope you can help me! Thanks 

[andrew_hardwick]

Hi @Min_02 ,

Sure, I created this measure in the Incident Table.

Survey Responses = CALCULATE(counta(Survey[Name]),USERELATIONSHIP(Survey[Created Date],'Date'[Date]))

I cannot remember exactly what I did next (it was 3 years ago 😀), but presumaby I just used it in my visualisations.

Regards,

Andy