topic Re: Circular relationship in Report Server

Circular relationship

Anonymous — Tue, 07 Jan 2020 11:32:11 GMT

Hello,

I´m creating a new Report and need to create a relationship between the Date and Customers tables to calculate de number of Customers whose registration date is equal to the selected date.

I can´t create this relationship because this causes a circular relationship.

Can you help me to solved this problem?

Re: Circular relationship

az38 — Tue, 07 Jan 2020 11:57:19 GMT

Hi Anonymous

it depends on full your data model, but try to set Both direction on either Clientes-Date or Cabecera-Date relationships

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Circular relationship

Anonymous — Tue, 07 Jan 2020 12:14:42 GMT

Hi az38

Thanks for yor answer.

I can't set both directions on the Cabecera-Date relationship because I have different measures in the Cabecera table. Some of these measures use SAMEPERIODLASTYEAR.

Re: Circular relationship

az38 — Tue, 07 Jan 2020 12:20:20 GMT

Anonymous

that's what i mean: for help show please your data model (example of data, measures and point where the error appears)

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Circular relationship

Anonymous — Tue, 07 Jan 2020 13:13:55 GMT

Hello

I show you a data example on the picture with the measures. The relation between Cabecera and Clientes use the ID_CLIENTE column. The relacionship between Date and Cabecera use the column FECHA.

I need to calculate the number of new customers by month (second graph) and filter all report by year. Example:

Re: Circular relationship

d_gosbell — Tue, 07 Jan 2020 22:24:56 GMT

You should be able to solve this by creating an inactive relationship between the Date and Clientes tables

Then creating a measure with the USERELATIONSHIP function to activate this relationship for that measure.

Cliente Registrations = CALCULATE( countrows(Clientes), USERELATIONSHIP(Clientes[Fecha_Alta], Dates[Date]))

This will allow you to see other measures using the active relationship, but just for the count of client registrations to use the relationship between Clientes and Dates

eg.