merging tables with two+ columns in common and additionally filtering out duplicates in merged table

[CL7777]

I am an advanced beginner dax user and am trying to do something above my head. I have tried so many ways to do it and it doesnt work.

First issue is that I have a table that has rental store transfer data on it ('rental data')

It looks like this:

Store      date         PartNum    quantity      Transaction #

1           10-2            AAA               -1                100

2            10-2           AAA                1                 100

3            11-6           BBB                -1                 101

4            11-6           BBB                 1                  101

This tabel shows that part AAA was transferred from store 1 to store 2 and part number BBB was transferred from store 3 to store 4 on the dates indicated.

From this table, Id like to create a table that looks like this

FromStore      ToStore       date         PartNum    quantity    transaction number

    1                        2           10-2            AAA               1               100

   3                         4            11-6           BBB                1               101

I have tried so many things and cannot get it to work. 

The second thing I would like to do is:

 Filter out any duplicate entries, as the original data set has some bad data. So, for example if in the first example table above, I had data that looked like the following data (below, Notice the duplicate entries for part number AAA on the same date), I would like to only include in my table the highest transaction number pair (101) and not include transaction 100.

Store      date         PartNum    quantity      Transaction #

1           10-2            AAA               -1                100

2            10-2           AAA                1                 100

3            10-2           AAA                -1                 101

4            10-2           AAA                 1                  101

Any help you guys could offer would be much appreciated!

[v-chuncz-msft]

@CL7777 

You may try using RANKX, LOOKUPVALUE and FILTER.

[danextian]

Hello @CL7777 ,

You may use the DAX formula below as a calculated column for your second requirement;

Is latest transaction = 
CALCULATE (
    MAX ( 'Table1'[Transaction #] ),
    ALLEXCEPT ( 'Table1', 'Table1'[PartNum] )
) = 'Table1'[Transaction #]

For your second requirement, additional info is needed. How would someone know which which among the stores a PartNum goes from and to if they have the same date and transaction #? For example, AAA could be from store 1 then moved to 2 or it could be the other way around. There must some kind of a field that indicates the chronological order of the transactions - a datetime/timestamp field perhaps.
 

[CL7777]

HI, thanks for your response. to follow up with answering your question, you tell the direction of tranfer for the part number by the fact that the quantity is -1. So, if it has a -1 quantity, that is the store that it left and if it has a positive 1 quantity, that is the store it was transferred to. Ill try your formula for the other part and see if that does the trick, thank you,