Forum Discussion
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
Time > time frame of 2 minutes
The orange lines are matching rows. The result is the min value of the index colum.
Greets,
Ronald
Power bi file
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])
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 ) ) )
11 Replies
Try this Custom Column
=let myproduct=[Product], myreceiver=[Receiver] in List.Min(Table.SelectRows(#"Added Index",each [Product]=myproduct and [Receiver]=myreceiver)[Index])
Thx for the reply either i'm missing some calculating conditions;
Date must be equal
Tag must be equalTime - time frame of 2 minutes
Greets,
Ronald
My bad :smileyembarrassed: I didn't look at your question carefully.
Just looked at the picture.
Will get back to you
