Cómo suprimir registros de accesos que se repiten en un corto espacio de tiempo usando DAX

[WestMart]

Estimados Amigos:

Necesitaría ayuda sobre cómo suprimir, usando el Query Editor de Power BI, ciertos registros en una tabla de control de accesos del personal.

El caso concreto se trata de que, al acceder al edificio, el personal registra su entrada en lectores que se almacenan en una base de datos. Pero a veces pasan varias veces la tarjeta y se registran varias entradas en un corto espacio de tiempo, y tendríamos que quitarlos  de la consulta para tener datos consistentes, y quisiera hacerlo usando DAX ( Query editor de Power BI).

La tabla tiene el formato que figura a continuación, y yo quería quitar registros para una misma persona que se repiten en un tiempo concreto.

Por ejemplo, quitar los registros realizados en un espacio temporal inferior a 5 minutos., por lo que en el caso de esta tabla se tendrían que suprimir las filas con IDAcceso 2 y 3, ya que apenas hay unos segundos entre un registro y otro.

IDAcceso	IDPersona	IDNivel	IDDispositivo	IDInstalacion	FechaHora	Fecha	Hora
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

Quería además, que el espacio temporal para el que se eliman las filas registradas fuese parametrizable, es decir, que pudiesen ser 5 minutos, ó 10 minutos, etc, y entiendo que esto lo tendría que hacer usando un parámetro.

Le he dado muchas vueltas para conseguir lo pretendido, pero aún no he llegado a una solución por lo que acudo a este fantástico foro, por si me podéis ayudar, cosa que agradecería enormemente.

Muchas gracias.

Saludos.

Agustín Martínez.

[v-kelly-msft]

Hola @WestMart ,

En primer lugar, cree una tabla de parámetros como se muestra a continuación:

Parameter = GENERATESERIES(5, 20, 5)

A continuación, cree una medida como se indica a continuación:

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

Y verás:

Aquí noté que el Access ID 6 también debe ser borrado ya que hay solamente unos segundos entre ID6 y el ID 5.

Para el archivo .pbix relacionado, pls ver adjunto.

Saludos
Kelly

¿He respondido a tu pregunta? ¡Marca mi puesto como una solución!

[WestMart]

Muchísimas gracias v-kelly-msft por tu respuesta tan clara y además acompañada de un ejemplo .pbix.

Te lo agradezco muchísimo y la medida que propones funciona perfectamente.

Aunque lo que buscaba era eliminar los registros de la tabla porque no son útiles y además me permitiría hacer mejor los análisis, tales como sumar accesos por persona, por dispositivo, etc. y según tu propuesta me es más complicado porque los registros siguen permaneciendo en la base de datos.

Aprovecharé tu útil aportación y seguiré investigando cómo suprimir dichos registro de la tabla, y seguramente la mejor opción será usando DAX o el lenguaje M desde el editor Query.

Me reitero en mis agradecimentos.

Un afectuoso saludo.

Agustín Martínez.

[v-kelly-msft]

Hola @WestMart ,

Si es así, es necesario hacerlo en la consulta de energía, utilizando el siguiente código M:

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"

A continuación, cree un parámetro de consulta como se indica a continuación:

Por último, filtre la columna según el parámetro:

A continuación, las filas que están por debajo de la duración se filtrarán.

Para el archivo .pbix relacionado, pls ver adjunto.

Saludos
Kelly

¿He respondido a tu pregunta? ¡Marca mi puesto como una solución!

[WestMart · Accepted solution]

Hola Kelly, ¡Fantástico! ¡Maravillosa aportación!.

Funciona perfectamente y esto es justo lo que estaba buscando y me va a ayudar mucho en informe de acceso.

Muchísimas gracias por la ayuda y estoy a tu disposición para lo que necesites.

Un afectuoso saludo,

Agustín 

[mahoneypat]

Usted indicó en su publicación que la solución debe usar "DAX (Editor de consultas)". En realidad, esas son dos cosas diferentes (el editor de consultas usó el lenguaje "M". Esto es complicado, pero se puede hacer de un par de maneras. Por favor, aclare si desea

1. Para establecer un parámetro estático (pero modificable) que se utilizará para eliminar filas de réplica dentro del marco de tiempo del parámetro cada vez que se actualicen los datos

O

2. Mantenga todas las filas en el modelo de datos, pero tenga un control deslizante en su informe donde los usuarios del informe pueden cambiar el período de tiempo para incluir/excluir réplicas (1 min, 2 min, 5 min, etc.).

Además, será más fácil y más eficaz hacer que las bandejas de tiempo y contar las personas / golpes de tarjeta dentro de cada bin, pero siempre hay un riesgo de que dos golpes cerca en el tiempo estarán en diferentes bins (final de uno, inicio de la siguiente). Comparar realmente cada fila/deslizar para confirmar que no hay otras filas por misma persona dentro del período de tiempo especificado podría ser un cálculo intensivo (pero posible) si se necesita capacidad de respuesta a las segmentaciones de datos.

saludos

palmadita

[WestMart]

Muchas gracias por su respuesta mahoneypat :

Con respecto a su petición de aclaración, indicarle que deseo la opción 1. que Ud. propone: establecer un parámetro estático (pero modificable) que se utilizará para eliminar filas de réplica dentro del marco de tiempo del parámetro cada vez que se actualicen los datos.

De esa manera ya no figurarán esos registros no deseados y que no son útiles para el análisis de datos.

Muchas gracias por la ayuda y espero ansioso la solución.

Un saludo.

Agustín Martínez.