Power BI Visualize Many to Many

Question

I currently have two tables: A "Send ID" table and an "Affiliation Table" each based on a column of customer IDs.

No columns have purely distinct values so I cannot create a many to one relationship.

I would like to visualize the Send IDs based on the Affiliations as shown here: Desired Output

I can work with either having the Send IDs repeat per affiliation in the new desired table or have them unique per affiliation - either way works with me.

Any help would be appreciated. Thank you

you can create a unique values table. Create a copy of one table, delete all the other columns but ID and then use formula VALUES() or simply remove duplicates — Ivancito
@Ivancito I don't want uniques in my table. If you look at the Desired Output image, I'm looking to create that third table — Sebastian Hubard

Ronan OMalley Ronan OMalley · Accepted Answer · 2019-11-21T01:53:39

noyraz's solution in establishing a many to many relationship based on the customerID should suit your needs.

If you are required to find out where a customer appears in the affiliation table or sendID table, I highly recommend performing a full outer join in the query editor.

Using the picture below, right click on any of the tables, and select reference.

Reference Screenshot

Then rename the table if you like
Click on Merge Queries
In the drop down, select the other table you didnt reference, then click on both customerIDs
select full other join.

Full Outer Join labled screenshot

Expand the new table column
deselect the ID if you like.

Expanding Column Screenshot

If there are occurrences where they don't appear (useful for sending and delivered tables), you can do visual level filters to see where either the Affiliation or SendID is null/blank.

Power BI Visualize Many to Many

2 Answers