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
WestMart
Helper I
Helper I

How to delete repeating access logs in a short space of time using DAX

Dear Friends:

You would need help suppressing, using the Power BI Query Editor, certain records in a personnel access control table.

The specific case is that, when accessing the building, staff record their entry into readers who are stored in a database. But sometimes they pass the card several times and multiple entries are recorded in a short space of time, and we would have to remove them from the query to have consistent data, and I would like to do so using DAX (Query Editor power BI).

The table is formatted below, and I wanted to remove records for the same person that are repeated at a specific time.

For example, remove records made in a temporary space of less than 5 minutes., so in the case of this table rows with Access ID 2 and 3 would have to be deleted, since there are only a few seconds between one record and another.

IDAccesoIDPersonaIDNivelIDDispositivoIDInstalacionFechaHoraDateTime
1112557824/06/2020 8:1924/06/20208:19:16
2112557824/06/2020 8:1926/06/20208:19:19
3112557824/06/2020 8:1924/06/20208:19:22
4112557825/06/2020 9:1925/06/20209:19:11
5112557824/06/2020 18:3624/06/202018:36:09
6112557824/06/2020 18:3624/06/202018:36:17
7112557825/06/2020 18:3125/06/202018:31:55
8212557828/06/2020 9:2428/06/20209:24:52
9312557828/06/2020 9:2428/06/20209:24:59
10412557828/06/2020 9:2528/06/20209:25:04

I also wanted the temporary space for which the registered rows are deleted to be parameterizable, that is, that they could be 5 minutes, or 10 minutes, etc., and I understand that this would have to be done using a parameter.

I have given him many turns to get what was intended, but I have not yet come to a solution so I come to this fantastic forum, in case you can help me, which I would greatly appreciate.

Thanks a lot.

Best regards.

Augustine Martinez.

1 ACCEPTED SOLUTION

Hello Kelly, Fantastic! Wonderful contribution..

It works perfectly and this is just what I was looking for and it will help me a lot in access report.

Thank you very much for the help and I am at your disposal for whatever you need.

An affectionate greeting,

Agustín

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

Hi @WestMart ,

 

First create a parameter table as below:

Parameter = GENERATESERIES(5, 20, 5)

Then create a measure as below:

Measure = 
var _ID=CALCULATE(MAX('Table'[IDAcceso]),FILTER(ALL('Table'),'Table'[IDPersona]=MAX('Table'[IDPersona])&&'Table'[IDAcceso]<MAX('Table'[IDAcceso])))
var _previoustime=CALCULATE(MAX('Table'[FechaHora]),FILTER(ALL('Table'),'Table'[IDPersona]=MAX('Table'[IDPersona])&&'Table'[IDAcceso]=_ID))
Return
IF(_previoustime=BLANK(),MAX('Table'[FechaHora]),IF(ABS(DATEDIFF(_previoustime,MAX('Table'[FechaHora]),MINUTE))<SELECTEDVALUE(Parameter[Parameter]),BLANK(),MAX('Table'[FechaHora]))
)

And you will see:

Screenshot 2020-10-27 174859.png

Here I noticed that Access ID 6 should also be deleted as there are only a few seconds between ID6 and ID 5.

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Thank you very

I thank you very much and the extent you propose works perfectly.

Although what I was looking for was to delete the records from the table because they are not useful and would also allow me to do better the analyses, such as adding accesses per person, per device, etc. and according to your proposal it is more complicated because the records still remain in the database.

I will take advantage of your useful contribution and continue to investigate how to delete such record from the table, and surely the best option will be to use DAX or the M language from the Query editor.

I reiterate myself en mis thanks.

An affectionate greeting.

Augustine Martinez.

Hi @WestMart ,

 

If so ,you need to do it in power query,using below M code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdBbCsQgDAXQrQx+F2pi4iNbEfe/jWliKa0zg6XzIbQXPSS3VgduOQ5uhzll/aTVxxU9+lcWKJdk+9FMILq2VHt1T4gfQjEhPJ8B0QT6IfAhlF3gk1BsBjCBpzNAlhDHISwU3/eI/xiQzEjTTfQ6jKtYKMxm5P3taORTG0iXxNpAEu59alXhodC7AA1oRvAXgsWTa+0N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDAcceso = _t, IDPersona = _t, IDNivel = _t, IDDispositivo = _t, IDInstalacion = _t, FechaHora = _t, Date = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IDAcceso", Int64.Type}, {"IDPersona", Int64.Type}, {"IDNivel", Int64.Type}, {"IDDispositivo", Int64.Type}, {"IDInstalacion", Int64.Type}, {"FechaHora", type text}, {"Date", type text}, {"Time", type time}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"FechaHora", type datetime}}, "en-SB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each let 
id = [IDPersona], index = [IDAcceso],
tab = Table.SelectRows(#"Changed Type with Locale",each [IDPersona]=id and [IDAcceso]=index-1)
in 
if Table.IsEmpty(tab) then
null
else
Table.Max(tab,"IDAcceso")[FechaHora]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if
[Custom]=null then Time.Hour([FechaHora])*60+Time.Minute([FechaHora])+Time.Second([FechaHora])/60
else Number.Abs(Duration.TotalMinutes([FechaHora]-[Custom])))
in
    #"Added Custom1"

 

Then create a query parameter as below:

Screenshot 2020-10-28 183131.png

Finally filter the column according to the parameter:

Screenshot 2020-10-28 183450.png

Then the rows which is below the duration will be filtered out.

Screenshot 2020-10-28 183555.png

For the related .pbix file,pls see attached.

 


Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

Hello Kelly, Fantastic! Wonderful contribution..

It works perfectly and this is just what I was looking for and it will help me a lot in access report.

Thank you very much for the help and I am at your disposal for whatever you need.

An affectionate greeting,

Agustín

mahoneypat
Employee
Employee

You indicated in your post that the solution should use "DAX (Query Editor)".  Actually, those are two different things (the query editor used "M" language.  This is tricky but can be done a couple way.  Please clarify if you want

 

1. To set a static (but changeable) parameter that will be used to delete replicate rows within the parameter time frame each time the data are refreshed

or

2. Keep all the rows in the data model but have a slider on your report where report users can change the timeframe to include/exclude replicates (1 min, 2 min, 5 min, etc.).

 

Also, it will be easier and more performant to make time bins and count people/card swipes within each bin, but there is always a risk that two swipes close in time will be in different bins (end of one, start of next).  To really compare every row/swipe to confirm there are no other rows by same person within the specified timeframe could be calculation intensive (but possible) if responsiveness to slicers is needed.

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you very much for your :

With regard to your request for clarification, please indicate that I wish option 1. you propose: set a static (but modifiable) parameter that will be used to delete replica rows within the parameter time frame each time the data is updated.

That way those unwanted records will no longer appear and are not useful for data analysis.

Thank you very much for the help and look forward to the solution.

Greetings.

Augustine Martinez.

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.