cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
boykin188 Frequent Visitor
Frequent Visitor

Many to many relationship


Hi everyone - very very new PBI user here. I have a problem where I have two datasets that use a common identifier - "PrimaryID."

 

Dataset 1 has roughly 3 million rows, where each row is a unique point in time for whatever ID it's attached to.

 

Dataset 2 has only 305,000, and also features multiple instances of the same ID.

 

There is crossover - the PrimaryID's for the smaller dataset appear in the larger one, and I want to analyze the larger dataset (1) using only ID's that show up in the smaller one (2). The big problem I'm running into is difficulty in managing the "many to many" cardinality of the relationships.

 

In Excel this would be a simple case of using, say, LOOKUP to create a column that could be used as a filter, but if there's a DAX option, or a PowerQuery option, I can't figure it out.

1 ACCEPTED SOLUTION

Accepted Solutions
boykin188 Frequent Visitor
Frequent Visitor

Re: Many to many relationship

OK, I may have solved this myself...please someone correct me if I'm wrong Smiley Happy 

- I created a duplicate of the smaller table in PowerQuery where I want to only use ID's which appear in it 

- Removed other columns, then removed duplicates 

- Created a one-to-many relationship with the bridge table to Dataset 1

-Can use PrimaryID from the bridge table as a filter

2 REPLIES 2
boykin188 Frequent Visitor
Frequent Visitor

Re: Many to many relationship

OK, I may have solved this myself...please someone correct me if I'm wrong Smiley Happy 

- I created a duplicate of the smaller table in PowerQuery where I want to only use ID's which appear in it 

- Removed other columns, then removed duplicates 

- Created a one-to-many relationship with the bridge table to Dataset 1

-Can use PrimaryID from the bridge table as a filter

v-danhe-msft Super Contributor
Super Contributor

Re: Many to many relationship

Hi @boykin188 ,

It is pleasant that your problem could be solved, could you please mark one reply as answer to close this topic?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 2,796 guests
Please welcome our newest community members: