Forum Discussion
Connecting 2 columns
Hi Jjay006 ,
Relationship in power bi is different than other databases, it has direction for relationships(e.g, single filter direction, filter effect from one side to other side; cross filter direction, each side can effect correspond column).
Power bi will try to analytics data model and summary records through relationships.
If your relationship map contains multiple active relationships to analytic summarize, it will confused which one should be the right one. So it only allow one activated major relationship link between linked tables for analytics and interactions.
According your provided ,[Calender table] and [Claim Result] 1:* both filter relationship, and [Claim Result]and [Table] *:* both filter relationship , Power BI could not recognize [Calender table] and [Table] relationship.
And you could refer the below soultions:
1. turn off bidirectional cross filtering on all your tables.
2. Hide the foreign key in every data table. This way users won't accidentally use is instead of the primary key in the lookup table.
3. When (and only when) you need a bi directional relationship for what you are doing, turn it on for that single table.
4. If you ever need 2 that conflict, then use the many to many pattern. refer: https://www.powerpivotpro.com/2014/08/filters-can-flow-up-hill-via-formulas-that-is/ and also here http://exceleratorbi.com.au/many-many-relationships-dax-explained/
You can also take a look at following blog about 'power bi' relationship direction:
What is the Direction of Relationship in Power BI?
Similar question refer:
https://community.powerbi.com/t5/Desktop/Bi-directional-cross-filtering-ambiguity/m-p/119890
https://community.powerbi.com/t5/Desktop/Ambiguity-relationship/m-p/560891
Did I answer your question? Mark my post as a solution!
Best RegardsLucien
Hi Jjay006 ,
Relationship in power bi is different than other databases, it has direction for relationships(e.g, single filter direction, filter effect from one side to other side; cross filter direction, each side can effect correspond column).
Power bi will try to analytics data model and summary records through relationships.
If your relationship map contains multiple active relationships to analytic summarize, it will confused which one should be the right one. So it only allow one activated major relationship link between linked tables for analytics and interactions.
According your provided ,[Calender table] and [Claim Result] 1:* both filter relationship, and [Claim Result]and [Table] *:* both filter relationship , Power BI could not recognize [Calender table] and [Table] relationship.
And you could refer the below soultions:
1. turn off bidirectional cross filtering on all your tables.
2. Hide the foreign key in every data table. This way users won't accidentally use is instead of the primary key in the lookup table.
3. When (and only when) you need a bi directional relationship for what you are doing, turn it on for that single table.
4. If you ever need 2 that conflict, then use the many to many pattern. refer: https://www.powerpivotpro.com/2014/08/filters-can-flow-up-hill-via-formulas-that-is/ and also here http://exceleratorbi.com.au/many-many-relationships-dax-explained/
You can also take a look at following blog about 'power bi' relationship direction:
What is the Direction of Relationship in Power BI?
Similar question refer:
https://community.powerbi.com/t5/Desktop/Bi-directional-cross-filtering-ambiguity/m-p/119890
https://community.powerbi.com/t5/Desktop/Ambiguity-relationship/m-p/560891
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien