Bridge table

[ZaraSeif123]

Hi All

I'm new to Power BI and have imported these two tables from salesforce in to my model.

'Created by' and 'Sold by' columns contain people names.  I need to make a bridge table here, because the relationship between these tables is many to many.

Couldn't find a way to create the bridge table.

Any help would be appreciated.

Thanks

Zara

[cjemmott · Accepted solution]

There may be a better way to do this, but here is what I did:

- Duplicate one of your queries (Edit Query -> right click the query from the list on the left and click "duplicate")

- Right click on the column header and select "Remove Other Columns"

- If there are null / missing values, click the small arrow on the right side of the column header and click "Remove Empty"

- Right click on the column header and select "Remove Duplicates"

That gives you a unique list to use as the bridge.

Then define two relationships, a many to one from 'Created by' to the bridge, and a one to many from the bridge to 'Sold by'.  Make sure both relationships are set to filter in both directions, and you should be good to go!

[Anonymous]

@cjemmott i did the same way...but i am still having issues....i can only bring columns from bridge table and either of the other two tables and not both....i want to show columns from all three tables and i am getting same 'cant build relationship issue'?any help?

[Anonymous]

I'm having the same issue. I tried turning on composite models to enable many to many relationships, but preview features weren't available. Power BI Desktop optimized for Report Server doesn't allow many to many relationships. I brought in a table that has unique values for part and created a one to many to the two tables I need to join. I'm still only able to bring in data from 2 of the 3 tables. I would just create the join in SQL, but the data is coming from two separate SQL servers and I don't have privileges to join on the servers. I'm going to try the bridge table again, but this just feels much harder than it should be. In Tableau, you literally just drag the table onto the workspace, click join and on what column. Done. I've been evangelizing Power BI over Tableau, but this issue is one that is making me question that decision. We can't have business users trying to create reports battling with a simple join for hours or days.

[gil]

hi

please visited your site

gilsonite

natural asphalt

[ZaraSeif123]

Hi cjemmott

Thanks for your answer, but the rows aren't redundant and I need to keep them, because the count of them is importand for me and I should show the count ot the rows in my matrix.

Thanks

Zara

[cjemmott]

I edited my answer for clarity - let me know if that still isn't what you were looking for.

[ZaraSeif123]

Hi

Yes it helped a lot, but I still can't show all the information I need in one matrix. I already have the correct numbers in two separate matrixes but they should be shown in one.

my model looks like this now:

I'm trying to merge the two top matrixes into one but the columns aren't showing the way I need it.

Thanks

Zara

[v-yuezhe-msft]

@ZaraSeif123 ,

Could you please share me the PBIX file for analysis?

Regards,
Lydia

[ZaraSeif123]

Thanks everybody. The problem is solved.

[Blackjack]

What's the point of only saying the problem is solved without sharing the final solution that solved your issue?
Someone might run into the same problem and won't be able to find the answer here. Just saying.   

[ZaraSeif123]

Hi

I've accepted one of the replies as the solutions.