Problem with creating a satisfactory relationship model

[LauritzBL]

Hello

I work at a medium-sized company as a data scientist. We began to work with Power BI a couple of months back, and are therefore quite new to the game. We are currently working on creating a relationship model which would fit the most of our needs, and be the base for multiple reports on the online service. 

We have some challenges figuring out exactly how the tables in our model should be related to each other without creating circular dependencies.

We have a customer database where we register single (Gifts) or recurring donations (Recurring Gifts), and where these donations come from (Packages) for a contact.

Our problem is that we in our MSSQL database (where we get our Power BI data from) also have relationships between Packages <-> Gifts, Packages <-> Recurring Gifts and Packages <-> contact. 

So far we have tried to import the model as-is from our MSSQL database with the result that Power BI deactivates atleast one of the relationships coming packages. It is not possible to activate these relationships manually as it would create a circular dependency.

An idea that we have for a solution is to create a mapping table between Packages and contacts, Gifts, Recurring Gifts.
However, this solution would be rather cumbersome to both create and use as it would create an unnecessary abstraction between our data. Furthermore, it would also likely introduce a circular dependency between Recurring Gifts, Gifts and the mapping table.

If you have any suggestions as to how to solve our problem, please leave a comment.

All help is appreciated!

[v-yuezhe-msft]

@LauritzBL,

What error message do you get when you create relationship between Packages <-> contact, Packages <-> Recurring Gifts?

In your scenario, you can set cross filter direction of relationship between Gifts and Recurring Gifts from “Both” to “Single” , that way, you should be allowed to make the relationship between the Packages and Recurring Gifts active.

Regards,
Lydia

[noyrotbart]

Hey. My name is Noy and I'm the head of the department in this organization.

Thanks for the input!

An initial problem that was not conveyed in the first post concerns the connection between contact and gifts.

To make it clear, contacts can give single gifts, or regular gifts (once a month), which spawn a chain of gifts.

We would be very happy to have all connections active, but this seem to be not possible.

Is there a workaround to this scenario?

[v-yuezhe-msft]

@noyrotbart,

Could you please post the full error message in Power BI Desktop?

Regards,
Lydia

[noyrotbart]

Sure:

"You can't create a direct active relationship between Gifts and contact because an active set of indirect relationships already exists between these tables. To make this relationship active, set the Cross filter direction to "single", delete, or deactivate any of the indirect relationships first".

(At this point I also note that one can't copy the errors :))

[v-yuezhe-msft · Accepted solution]

@noyrotbart,

Please review my reply in the similar thread below. 

http://community.powerbi.com/t5/Desktop/Can-t-create-direct-active-relationship/td-p/148425

Regards,
Lydia

[noyrotbart]

Well, one can pass that with a many to many relation at least, no?

[v-yuezhe-msft]

@noyrotbart,

In Power BI Desktop, you can create many-to-many relationship between tables with  the help of bridge table.

Regards,
Lydia