Forum Discussion
Connecting 2 columns
Hi,
I need to connect two columns "Column - Model" from Page 1 and "Column - Model" from Page 2.
Both have same values. May i know how to merge them into single column and use a common column to filters same values in different pages.
Thanks
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
6 Replies
Jjay006 , Are they from the same table or a different table.
You can create a new table
new table = = distinct(union(distinct(Table1[Column 1]),distinct(Table2[Column 2])))
if they are from two tables you will have active join with both tables and use the column from this table
if they are from one table, one join will be inactive , you need userelationship in one measure
They are from different tables.
I have attached an Image. My question is when i select a option "622B" - Model Table 1 based on that selection even on Model table 2 only "622B" data should be filtered .
Hi,
That code worked but the table isnt connected to other tables, and i am unable to link new table to claim[Results] table. May i know how to fix this?
Jjay006 , Filter blank , then join should work
filter(distinct(union(distinct(Table1[Column 1]),distinct(Table2[Column 2]))), not(isblank([Column 1])) )
I used filter option but using that i can only connect to one table but not to another table.
May i know where should i use DISTINT(FILTER)...... code?
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
