I need to create a link between 2 tables in PowerPivot but my problem is that both tables don't have a unique Item.
The MainTable contains data that I cannot change since I receive them as they are, it contains transactions for specific items on specific dates and with other criteria involved, so the same item can be repeated several times even on the same date based on the criteria involved.
The Second table describes the status of this Item at a specific quarter period of the year, so the same item can have a different status during different quarters of the year (Note: only 2 status are present A & B and not all items have a status, so some items can have no status and therefore not mentioned in the second table)
I need to link them in such a way that I can figure out what was generated by items that had a specific status at a certain period.
For example: What was generated by items that had status A during the period Q2
I created a sample file to explain my problem, in it there is a third sheet to make the link between the quarters and the dates (I'm not sure if it is needed, but I don't know how to link them otherwise)
I can redesign the second table anyway that can solve the problem, even divide it to several tables if that helps
P.S.: I tried to create 4 tables for the status table, one for each quarter, but then when I link them the pivot only reads the status from the first table and disregards the others...
Help pleaseeee...
Bookmarks