cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

How to relate these two Many to Many Tables

Dears:

 

First of all I have to say I'm really happy with the performance of PowerBI, Visuals and plantaform runs smoothly, but I have a problem I don't know how to handle.Smiley Frustrated

 

I have two tables:

  1. Table 1: This table groups all material receptions done in our organisation from 2014 to 2016 year to date. Every reception has its own batch number (and the worth/turnover) excepts non-stockable products (around 20% of the lines), where Batch number is empty. This table has (right now) around 5500 records. This table shouldn't be a many to many table because every batch should be different to each other, but it is not happening always, so some duplicates are present, so...

 

  1. Table 2: This table shows those batches where a quality problem came up. This is also a many to many table, because in the case of a batch has two quality problems (two reasons to be faulty), the program generates two records/lines, same batch number and same QualityClaimID, differing by the quality category.

 

Variables common: Supplier Name, Supplier Number, Item number, Batch number, reception date and others. These variables are present in both Queries. Smiley Wink

 

Action done: I have created a bridge table (with PowerQuery integrated), using, firstly, all the item numbers from the receptions table, then removing duplicates and then linking both tables together.

I did the same approach using the batch number instead but the my problem remains. Smiley Embarassed

 

Question: How to obtain the % of batches with quality-claims against the total of batches PER SUPPLIER.

 

My attempt: After connecting both tables (either by item or by batch) and by using DAX formula (Basically Distintcount) I could obtain the % of total batches with claim against the total batches received. The results is ok, around 3%.

After adding some Slicers like "Year from the reception date", Category, etc it works, but it works AT TOTALS LEVEL.

 

 

My problems comes when I use that measure at supplier level (you want to drill into details). It basically doesn't work giving me results without sense.... I have one supplier, for instance, with 3 batches claimed from 300 received, the results should be 1%, but it isn't.

I would like to create a Scatter chart that relates turnover with nº of batches received (both variables from receptions tables), being the size bigger depending on nº of claims (it comes from table 2)

The only solution I found is by using Vlookup formula in the excel files, adding the QualityClaimID from Table 2 to Table 1 (reception) where applicable, and then use Distinctcount

 

Can someone help me with this to make it much easier? is there any other way to connect this files and not be in the need of using this vlookup or Index-match functions?

 

Note: I cannot publish pictures or real pictures because the material is strictly confidential.

 

Thanks in advance,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: How to relate these two Many to Many Tables

This absolutely should work. I often work with 6 lookup tables in my data set with several created in the same manner. I do keep a 'stock' date table and item table but most others are ad hoc.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Solution Sage
Solution Sage

Re: How to relate these two Many to Many Tables

Did you read the articles on sqlbi.com about many-to-many relationships - they are awesome...

 

http://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/

 

/sdjensen
Highlighted
Super User I
Super User I

Re: How to relate these two Many to Many Tables

I have worked with several issues along the same lines. These best answer I  have found is to merge or append the tables together into one big table and build from there. It isn't difficult and, at least for me, has returned reliable results.

You can find merge and append in the query editor.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate III
Advocate III

Re: How to relate these two Many to Many Tables

Hi Kcantor:

 

Thanks a lot for the reactivity and prompt answer.

 

I'm familiar with merge and append specially becuase I used both of them in PowerQuery sometimes. But in this case I think it is not the appropiate solution because Receptions table and Non-compliants table differ a lot each other.

 

It could possible to join, but most of the colums in Receptions table will be blank. Doing in the other way around, I would lose a lot of information needed for other graphs/visuals.

 

Thanks anyway!

Highlighted
Advocate III
Advocate III

Re: How to relate these two Many to Many Tables

Hi Sdjensen:

 

Your file results quite interesting, it explains quite well how single-both directions relathionships work. In fact I also found a article in PowerBI community as a Link within and well-traslated to Spanish which always helps.. these things are new for me, so reading in mother language makes much easier hehehe.....

 

I am going to carry out some test, I will post my posible solution.

 

Regards,

Highlighted
Advocate III
Advocate III

Re: How to relate these two Many to Many Tables

Dears:

 

I think I have found a possible solution that maybe works, but I really want to check with you in order to know if this lucky or it's make sense.

 

Tables (fact tables): Let me remind the problem I had:

 

  • Two Tables, one table with all the receptions done per supplier, many to many type, where supplier name, product category, batch number, turnover/worth is indicated.
  • Second table with all the batches rejected from the list of receptions. Logically less rows, and Batch number as common variable. Also many to many type.

Purpose: To allow make calculations like:

 

  • Number of batches rejected over the total of batches delivered by supplier with the total worth value/turnover (of each supplier)
  • Same thing by supplier and by item
  • Same approach by product category

 

To do this I have done the following:

 

  1. Create a cross-bridge-vlookup table with all the "items number" from reception table, then removing duplicates, creating thereby many to one relathionships with the ReceptionTable and QualityClaimsTable. You can only can fix cardianialty "Both" with one table, the other one must be single (thanks Sdjenses for the tutorial Smiley Happy).
  2. Do the same thing with the Product categories.
  3. Do the same thing with the Year
  4. Do the same thing with the Batch number.

Once finished, and once the relathionships are created (I repeat, one has both coordinality, the other one just Single), you can create measures and tables where the system works.

Structure once finished: 4 vlookups tables + 2 fact tables

After doing this, the system is working and I can create tables by selecting any of these variables, providing correct data and allowing filtering, ordering, cross-filtering, measures, etc.

 

My conclusion is therefore: You have to create as many bridge-cross-vlookup tables as variables you want to use for drilling your data between these two many-many tables (fact tables).

 

I'm looking forward to hearing from you 🙂

 

Regards,

 

Highlighted
Super User I
Super User I

Re: How to relate these two Many to Many Tables

This absolutely should work. I often work with 6 lookup tables in my data set with several created in the same manner. I do keep a 'stock' date table and item table but most others are ad hoc.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors