March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
IDAcceso | IDPersona | IDNivel | IDDispositivo | IDInstalacion | FechaHora | Date | Time |
1 | 1 | 1 | 2 | 5578 | 24/06/2020 8:19 | 24/06/2020 | 8:19:16 |
2 | 1 | 1 | 2 | 5578 | 24/06/2020 8:19 | 26/06/2020 | 8:19:19 |
3 | 1 | 1 | 2 | 5578 | 24/06/2020 8:19 | 24/06/2020 | 8:19:22 |
4 | 1 | 1 | 2 | 5578 | 25/06/2020 9:19 | 25/06/2020 | 9:19:11 |
5 | 1 | 1 | 2 | 5578 | 24/06/2020 18:36 | 24/06/2020 | 18:36:09 |
6 | 1 | 1 | 2 | 5578 | 24/06/2020 18:36 | 24/06/2020 | 18:36:17 |
7 | 1 | 1 | 2 | 5578 | 25/06/2020 18:31 | 25/06/2020 | 18:31:55 |
8 | 2 | 1 | 2 | 5578 | 28/06/2020 9:24 | 28/06/2020 | 9:24:52 |
9 | 3 | 1 | 2 | 5578 | 28/06/2020 9:24 | 28/06/2020 | 9:24:59 |
10 | 4 | 1 | 2 | 5578 | 28/06/2020 9:25 | 28/06/2020 | 9: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.
Solved! Go to 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
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:
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 much v-kelly-msft for your very clear response and also accompanied by a .pbix example.
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:
Finally filter the column according to the parameter:
Then the rows which is below the duration will be filtered out.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you very much for your response mahoneypat :
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
169 | |
144 | |
90 | |
70 | |
58 |