Error in relationships

[KatkaS]

Hello,

I have two data tables (excel sheets) and I'm poorly trying to create very simple relationships between periods and divisions of these tables to be able to select based on these two criterias ..

I was successful with Data_SAP, but when I'm trying to create relationship between DATA_BPCS_KPI and Report day based on the Period and Reporting period, I get following error... I don't understand why should I deactivate relationships between 02 Data_SAP and 02 Data_BPCS - I don't have any such relationship active....?

I hope my question is not confusing and thank you very much for your help!!

[HotChilli · Accepted solution]

The error is telling you that if powerbi allowed the relationship to be created, it would have more than one way to get from 02 Data SAP to 02 Data BPCS ( follow the arrows of the relationships to test this yourself)  and that is not allowed because "it creates ambiguity" i.e. which route should powerbi take to link the tables.

So you have to go without the relationship or re-work your data model.

An observation : there are 2 m:n relationships and 2 bi-directional relationships.  These must be handled with care. I suggest looking at your model from the start and try to decide what you want to do with any visualisation. Then keep your model as simple as possible.

[v-xicai · Accepted solution]

Hi @KatkaS ,

You may needn't to create the relationship between the table 02 Data_BPCS_KPI and table 06 Report day , just change the Cross filter direction of the relationship between the table 04 FAMs and table 02 Data_BPCS_KPI from Single to Both, which will take all the tables above treated as a single table.

You can learn more about relationship :https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KatkaS]

Thank you, Amy, for the link, Relationships are difficult topic for me to understand:)

I've changed cardinality to many to one and Cross filter to Single for all and it worked.. 

[HotChilli · Accepted solution]

The error is telling you that if powerbi allowed the relationship to be created, it would have more than one way to get from 02 Data SAP to 02 Data BPCS ( follow the arrows of the relationships to test this yourself)  and that is not allowed because "it creates ambiguity" i.e. which route should powerbi take to link the tables.

So you have to go without the relationship or re-work your data model.

An observation : there are 2 m:n relationships and 2 bi-directional relationships.  These must be handled with care. I suggest looking at your model from the start and try to decide what you want to do with any visualisation. Then keep your model as simple as possible.

[KatkaS]

Thank you, HotChilli, for your time!

I don't have any relationship between 02 Data SAP to 02 Data BPCS , that's why the error message is confusing to me..

I've changed Relationships from many to one and Single..