How to relate these two Many to Many Tables

[Ciria]

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.

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.

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.

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,

[kcantor]

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.

[Ciria]

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!

[sdjensen]

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/

[Ciria]

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,

[Ciria]

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 ).
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,

[kcantor · Accepted solution]

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.