Hola a todos,
Tengo un problema simple que he resuelto usando Power Query, pero no creo que sea una solución viable a largo plazo debido al gran volumen de datos que produce. Lo que estoy buscando es una manera de replicar la funcionalidad de mi solución M Query pero con la delicadeza de dax.
Por lo tanto, el problema es simple. Tengo varias actividades de cumplimiento que los sitios están obligados a hacer en diferentes frecuencias, pero todas terminan generando una ventana de cumplimiento. En pocas palabras, si está terminado o permanece incompleto después de su fecha de vencimiento su rojo, si su dentro de siete días su ámbar y si tiene más tiempo dejó su verde. Uno de los requisitos clave de nuestro equipo es la capacidad de ver lo que era un estado de cumplimiento en una fecha en particular y también con el tiempo a través del gráfico de líneas de tendencia de cumplimiento para identificar sitios consistentes e individuos que están luchando para mantener su queja en el sitio. Es importante tener en cuenta que el SaaS del que estoy recopilando estos datos no nos permite hacerlo en su producto, por lo tanto, la dependencia de Power BI para esta información.
Para lograr esto, trabajo en la fecha de inicio y la última fecha y luego uso la función List.Dates para expandir todas las fechas y luego puedo hacer una simple medida dax para averiguar cuál es el porcentaje de cumplimiento. Lo que me gustaría hacer es eliminar la hinchazón creada mediante el uso de List.Dates y tener una solución completamente orientada a Dax. Sin embargo, mi M es más fuerte que mi dax y mientras he luchado con el problema ya que soy una banda de un solo hombre en mi organización no hay nadie para rebotar una o dos ideas. Sé que necesito ampliar las fechas y probar si la fecha MAX cae en ese rango y luego si lo hace para averiguar cuál es su estado; Rojo, Amarillo o Verde y luego contar el número de actividades que estaban activas ese día y en fecha sobre el número total de actividades activas.
He publicado un conjunto de datos completo a partir de esta mañana que se ha limpiado de datos confidenciales y se ha incorporado en mi código M a continuación. La consulta se denomina "Actividades de cumplimiento".
Dejar
Fuente: Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vTEM3okRxs1s5B6hGMGXcOBRQ8KcrAuFIsf2CYkDTPhXJc2WgkvTH6neS9aZlPwDg/pub?output-xlsx"), null, true),
Sheet1_Sheet de la fuente de la fuente de la fuente de la fuente de la fuente de la clase de la fuente de la clase de la fuente, de la clase de la clase de la hoja de datos,
"Encabezados promocionados" - Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars-true]),
"Filas filtradas" - Table.SelectRows(''Encabezados promocionados'", cada uno ([Prioridad] <> "") y ([Eliminado] <> "1")),
"Tipo modificado a Fechay Hora" - Table.TransformColumnTypes("Filas filtradas", "Siguiente", tipo datetime, "DateDueWhenDone", escriba datetime, "DOE", escriba datetime, "DateDeleted", "DateDeleted", tipo datetime?
"Tipo modificado a fecha" ? Table.TransformColumnTypes("Changed Type to DateTime","PropertyKey", Int64.Type,", "DateDueWhenDone", fecha de tipo, "DateDone", fecha de tipo, "NextDue", tipo de fecha, "FrequencyNumber", Int64.Type, "DateDeleted", tipo de fecha de ", "Status",
"Fecha de vencimiento agregada" - Table.AddColumn('Changed Type to Date','Fecha de vencimiento'', cada una si [DateDueWhenDone] - null then [NextDue] else [DateDueWhenDone]),
"Añadida última fecha" - Table.AddColumn('Fecha de vencimiento agregada", "Fecha de vencimiento" y si [DateDone] <> null y [DateDueWhenDone] - null y [NextDue] > DateTime.Date(DateTime.LocalNow()) entonces [NextDue] si [DateDone] <> null y [DateDueWhenDone] DateTime.Date(DateTime.LocalNow()) then DateTime.Date(DateTime.LocalNow()) else si [DateDone] <> null luego [DateDone] else if [DateDone] - null y [Due Date] < DateTime.Date(DateTime.LocalNow()) luego DateTime.Date(DateTime.LocalNow())
"Añadido ActivityRecordStartDateTemp" - Tabla.AddColumn('Añadido la última fecha", "ActivityRecordStartDateTemp", cada uno si [FrequencyMeasure] - "Semana" luego Date.AddWeeks([Fecha de vencimiento],-[FrequencyNumber]) si [FrequencyMeasure] - "Mes" y luego Date.AddMonths([Fecha de vencimiento],-[FrequencyNumber]) si [FrequencyMeasure] - "Año" y luego Date.AddYears([Due Date],-[FrequencyNumber])
"Fecha de inicio del registro de actividad añadido" - Table.AddColumn('Added ActivityRecordStartDateTemp", "Activity Record Start Date", each Date.AddDays([ActivityRecordStartDateTemp],1)),
"Estado del registro de actividad añadido" - Table.AddColumn("Fecha de inicio del registro de actividad añadido", "Estado del registro de actividad", cada uno si [Estado] - 30 y luego "Cerrado" si [Estado] - 20 entonces "Esperando documentación" si [Estado] - 10 y luego "Abrir" otro null),
"Añadido de fechas a la columna de la lista" - Tabla.AddColumn('Estado del registro de actividad añadido", "Fecha", cada uno ' Number.From([Fecha de inicio del registro de actividad]).. Number.From([Last Date]) ?
"Fecha ampliada" - Table.ExpandListColumn("Fechas agregadas a la columna de lista", "Fecha"),
"Fecha cambiada a la fecha de tipo" - Table.TransformColumnTypes(''Fecha expandida'','Fecha", fecha de tipo', ''Número de frecuencia'', 'número de código', ''Fecha de vencimiento'', 'fecha de vencimiento', ''DOE'', fecha de tipo ''),
"Fechas nulas eliminadas" - Table.SelectRows("Fecha cambiada a fecha de tipo", cada una ([Fecha] <> null)),
"Añadido atrasado" ? Table.AddColumn('Fechas nulas eliminadas','''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
"Estado de cumplimiento añadido" ? Table.AddColumn(''Added Overdue'', "Compliance Status", cada uno si [Fecha] > [Fecha de vencimiento] entonces "Rojo" si [Fecha] > Fecha.De(Número.From([Fecha de vencimiento])-7) entonces "Amber" else "Green"),
"Frecuencia de actividad agregada" - Table.AddColumn(''Estado de cumplimiento agregado'", "Frecuencia de actividad", cada uno si [FrequencyNumber] - "1" entonces [FrequencyNumber]&" "&[FrequencyMeasure] else [FrequencyNumber]&" "&[FrequencyMeasure]&"s"),
"Orden de estado de cumplimiento añadido" á Table.AddColumn("Frecuencia de actividad agregada", "Clasificación de estado de cumplimiento", cada una si [Estado de cumplimiento] - "Verde" entonces 1 si [Estado de cumplimiento] - "Amber" entonces 2 si [Estado de cumplimiento] - "Rojo" y luego 3 más nulo),
"Clasificación de prioridad agregada" - Table.AddColumn('Categoría de estado de cumplimiento agregado", "Clasificación de prioridad", cada una si [Prioridad] - "Alto" entonces 1 si [Prioridad] - "Medio" entonces 2 si [Prioridad] - "Bajo" y luego 3 si es nulo),
"Merge PropertyKeys for connected sites" ? Table.ReplaceValue('Added Priority Sort', 12848, 4328, Replacer.ReplaceValue, ''PropertyKey''),
"Eliminado otras columnas" ? Tabla.SelectColumns(?"Palabras de propiedad combinadas para sitios conectados","PropertyKey", "Actividad", "Categoría", "ActivityRecordKey", "DateDone", "Prioridad", "Estado", "Eliminado", "Fecha de vencimiento", "Fecha de la última fecha", "Fecha de inicio del registro de actividad", "Estado del registro de actividad", "Fecha", "Estado de cumplimiento", "Frecuencia de cumplimiento", "Frecuencia de actividad",
"Columna de fecha terminada renombrada" - Table.RenameColumns(''Eliminar otras columnas'', ''FechadeHecho'', ''Fecha hecha''',
"Fechas eliminadas antes de la formación de confianza" - Table.SelectRows("Columna de fecha renombrada terminada", cada [Fecha] > #date(2018, 9, 1)),
"Errores eliminados" - Table.RemoveRowsWithErrors("Fechas eliminadas antes de la formación de confianza", "Clasificación de estado de cumplimiento"-),
"Transformar columnas" ? Table.TransformColumnTypes(''Errores eliminados'''''''Categoría'', escriba texto', ''Actividad'', escriba texto', ''Frecuencia de actividad'', texto de tipo',"Estado de cumplimiento", escriba text, escriba text, escriba text, escriba text, escriba text, escriba text, "ActivityRecordKey", escriba text, "Activity Record Start Date", escriba text, escriba text, escriba text, escriba text, escriba text, escriba text, escriba Text, escriba Text, escriba text, escriba text, escriba text, escriba text, escriba text, texto de tipo, texto de tipo de texto, texto de tipo.
En
"Transformar columns"
La medida dax que utilizo para calcular el cumplimiento es la siguiente:
Compliance Percentage =
var ComplianceNumerator =
Calculate(
DISTINCTCOUNT('Compliance Activities'[ActivityRecordKey]),
('Compliance Activities'[Compliance Status] IN {"Green", "Amber"}),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] = MAX ( 'Calendar'[Date] )
))+0
var ComplianceDenominator =
CALCULATE(DISTINCTCOUNT('Compliance Activities'[ActivityRecordKey]),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] = MAX ( 'Calendar'[Date] )
))
Return
CALCULATE(
DIVIDE(
ComplianceNumerator,
ComplianceDenominator
)
)
