Forum Discussion

Ronald123's avatar
Ronald123
Resolver III
7 years ago
Solved

Matching transactions

Hello,   Can someone help me with the follow analyse? I need to find matching rows, the conditions are the follow. Date       > equal Product  > equal Receiver > equal TAG       > equal Tim...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Ronald123 

     

    Try this Custom Column

     

    Please takle a look at attached file

     

    =let myproduct=[Product], myreceiver=[Receiver],mytag=[TAG],mydate=[Date], mytime=[TIme2] in
List.Min(Table.SelectRows(#"Changed Type1",each [Product]=myproduct and [Receiver]=myreceiver and [Date]=mydate and [TAG]=mytag and Number.Abs((Duration.TotalMinutes(Duration.From([TIme2]))-Duration.TotalMinutes(Duration.From(mytime))))<=2)[Index])
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Hi Ronald123 

     

    My apologies for not getting back earlier

     

    Try this DAX column. It works with sample data.

    See if it performs better than M custom column

     

    Calculated Column =
CALCULATE (
    MIN ( Test[Index] ),
    FILTER (
        Test,
        [Product] = EARLIER ( [Product] )
            && [Receiver] = EARLIER ( [Receiver] )
            && [Date] = EARLIER ( [Date] )
            && [TAG] = EARLIER ( [TAG] )
            && ABS ( [TIme] - EARLIER ( [TIme] ) )
                <= TIME ( 0, 2, 0 )
    )
)
Ronald123's avatar
Ronald123
Resolver III
7 years ago

Zubair_Muhammad ,


When you have time to look at it?

Don't feel any pressure :)

 

Greets,

 

Ronald

 

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Hi Ronald123 

 

My apologies for not getting back earlier

 

Try this DAX column. It works with sample data.

See if it performs better than M custom column

 

Calculated Column =
CALCULATE (
    MIN ( Test[Index] ),
    FILTER (
        Test,
        [Product] = EARLIER ( [Product] )
            && [Receiver] = EARLIER ( [Receiver] )
            && [Date] = EARLIER ( [Date] )
            && [TAG] = EARLIER ( [TAG] )
            && ABS ( [TIme] - EARLIER ( [TIme] ) )
                <= TIME ( 0, 2, 0 )
    )
)