Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ronald123
Resolver III
Resolver III

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

 

Naamloos.png

2 ACCEPTED SOLUTIONS

@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])

Regards
Zubair

Please try my custom visuals

View solution in original post

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 )
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

11 REPLIES 11
Zubair_Muhammad
Community Champion
Community Champion

@Ronald123 

Try this Custom Column

 

=let myproduct=[Product], myreceiver=[Receiver] in
List.Min(Table.SelectRows(#"Added Index",each [Product]=myproduct and [Receiver]=myreceiver)[Index])

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad ,

 

Thx for the reply either i'm missing some calculating conditions;

Date must be equal
Tag must be equal

Time - time frame of 2 minutes

 

Greets,

 

Ronald

@Ronald123 

 

My bad  Smiley Embarassed I didn't look at your question carefully.

Just looked at the picture.

 

Will get back to you


Regards
Zubair

Please try my custom visuals

@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])

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad ,

 

I use the Power Query you've made in a calculation for a Google Big Query project.
The Google Big Query is created with a SQL with a max of 100.000 lines.

Either the Power Query calculation calculate over the more than 20 milion lines.

 

Also checked with a CSV with 100.000 lines with a total of 10 mb.
The query is running a quarter with a total of 12 GB loaded in the model.

 

 

Is there a solution for this issue?

 

Greets,

 

Ronald 

@Ronald123 

 

Try Table.Buffer,

 

IN the Query Editor>>View>>advanced editor

 

In the step just before the step in which custom column was added, try wrapping the code inside Table.Buffer

For example in the file I shared

 

    #"Changed Type1" = Table.Buffer(Table.TransformColumnTypes(#"Duplicated Column",{{"TIme2", type number}})),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each 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]))
in
    #"Added Custom1"

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad ,

 

Tanks for you're reply but table.buffer doesn't help.
In Power Query is't possible to run the calculation but is isn't possible to apply the query in the model.

On my personal computer i have memory issue's with this calculation and the server of my work is runing  and runing...

Is there an another option? Maybe DAX?

 

Greets,

 

Ronald

@Ronald123 

 

I will look into it


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad ,


When you have time to look at it?

Don't feel any pressure 🙂

 

Greets,

 

Ronald

 

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 )
    )
)

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad ,

 

Great solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.