Forum Discussion
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.
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,
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:
- 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 :smileyhappy:).
- Do the same thing with the Product categories.
- Do the same thing with the Year
- 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,
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.