Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sacheu
Employee
Employee

How to have 2 active relationships between 2 tables

I have 2 tables, each of them has its visual in PowerBI.
And there is currently 1 active relationship between them.


My question is how can I add a 2nd active relationship between them?
so that when I click on 1 item in 1 visual, the other table will get updated and Vice versa.

 

Table 1:

Column A

Column 1

Column 2

Column 3

Column B

 

Table 2:

Column A

Column 5

Column 6

Column 7

Column B

 

I want Column A and Column B to be active relationships.

 

Thank you.

Sam

 

9 REPLIES 9
parry2k
Super User
Super User

@sacheu you cannot have more than one active relationship, you have to use userelationship function to use inactive relationship.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

So if i have 1 active relationship and 1 inactive relationship. Each relationship maps to a different column of the tables.

How can i make the other visual update when i select 1 visual? I thought i need to have active relationship before i can get clicking 1 visual will update another visual working.

You'll likely need some kind of bridge table or combined index to relate the tables. Can you describe what are in those A and B columns?

Both ColumnA and ColumneB are string. And there can be multipe row with same value in Table1 and Table2.

So Table 1 and Table 2 shoul dhave a Many - To -Many relationships with ColumnA and ColumnB.

 

Althought there can be multiple row with same values in ColumnA and Column B, But the combination of ColumnA and ColumnB values is unique in Table 1 and Table 2.

 

 

 

OK, then a creating a common key/index/ID for each unique A,B pair in the query editor is a decent solution.

 

Here's an example of how this works:

http://radacad.com/relationship-in-power-bi-with-multiple-columns

Thanks. I read that post http://radacad.com/relationship-in-power-bi-with-multiple-columns.

But I don't understand how to apply to my problem. 😮

 

I have visual (piechart and a stackcolumn chart) for my Table 1, and Table 2 's visual is a table.

piechart is summarized based on ColumnA of Table 1.

stackcolumn is summarized based on Column B of Table 1.

 

Even if i create a calucated column in each of my Table  1 and Table 2,  i

how can I make powerBI display Table2 values according to what i selected in Table 1 visual?

e.g.

e.g. if i select a particular value of Column A in the pie chart,

Table2 only shows the rows with ColumnA equals to what is selected

 

same as if i select a value in Column B in the stacked column chart, 

Table 2 only shows the rows with the selected column B. 

If you have the index I suggested, then you can set the relationship to filter both directions, which should do exactly what you want.

Thanks for your solution. Having 1 index column let me to get to this state:

1. click visual for Column A of Table1, visual for Table 2 will be filted to show its Column A of what is selected

2  click visual for Column B of Table1, visual for Table 2 will be filted to show its Column B of what is selected

 

But how can I filter Table 2 of what is selected of Column A *AND* Column B of Table 1?

 

Since the index I suggested is for unique combinations of both A and B, it will cross filter both.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.