Many to many relationship

[boykin188]

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.

[boykin188 · Accepted solution]

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

- 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]

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