Recuento de ciclos

[JoeJoe666]

Hola

Soy nuevo en power BI. No estoy seguro de si necesito usar DAX o M para lo que necesito.

Me gustaría crear un recuento cíclico en una nueva columna basada en lo siguiente:

*para cada dispositivo

*cambiar el ciclo cuando el estado alcanza "1"

*Cada dispositivo tiene sus propios recuentos de ciclos

*los ciclos aumentan en 1

*los ciclos deben estar en orden cronológico por "tiempo", para cada dispositivo

Intenté lo siguiente sin éxito:

Ciclos: CALCULATE(DISTINCTCOUNT(Sheet2[Estado]),

FILTRO(Sheet2,

Sheet2[Time]<EARLIER(Sheet2[Time]) &&

Sheet2[Estado]<2 &&

Sheet2[dispositivo]-EARLIER(Sheet2[dispositivo])))

Nombre de la tabla: "Sheet2"

Hora	Dispositivo	m1	m2	m3	Estado	Ciclos
9/1/2020 0:01	4	-0.103	0	100	1	1
9/1/2020 12:56	4	-0.103	0	100	1	1
9/1/2020 13:26	4	-0.103	0	100	1	1
9/1/2020 13:26	4	-0.594	0	100	4	1
9/1/2020 13:31	4	-0.594	-0.1	100	4	1
9/1/2020 13:36	4	-0.654	-15	99.9	4	1
9/1/2020 20:31	4	0.02	-24.7	77.3	4	1
9/1/2020 20:33	4	16.927	0	77.8	3	1
9/1/2020 20:38	4	17.407	1.5	79.1	3	1
9/1/2020 20:58	4	17.331	7.3	84.4	3	1
9/1/2020 21:23	4	17.282	14.5	91.1	3	1
9/1/2020 21:27	4	17.284	15.9	92.4	3	1
9/1/2020 21:28	4	15.926	16.2	92.6	2	1
9/1/2020 23:23	4	2.269	25.6	101.3	2	1
9/1/2020 23:43	4	1.862	26.4	101.9	2	1
9/1/2020 23:44	4	-0.1	0	100	1	2
9/2/2020 0:14	4	-0.1	0	100	1	2
9/2/2020 7:19	4	-0.102	0	100	1	2
9/2/2020 7:24	4	-0.102	0	100	1	2
9/2/2020 7:26	4	-0.175	0	100	4	2
9/2/2020 7:31	4	-1.253	-0.1	99.9	4	2
9/2/2020 20:01	4	-0.025	-5	79.7	4	2
9/2/2020 20:06	4	-0.025	-15	79.7	4	2
9/2/2020 20:11	4	-0.025	-22.2	79.7	4	2
9/2/2020 20:15	4	7.011	0	80.1	3	2
9/2/2020 20:20	4	17.448	1.5	81.5	3	2
9/2/2020 20:35	4	17.376	5.9	85.5	3	2
9/2/2020 20:55	4	17.344	11.6	90.8	3	2
9/2/2020 20:58	4	16.897	12.5	91.6	2	2
9/2/2020 21:18	4	9.272	15.6	94.4	2	2
9/2/2020 23:08	4	2.274	22.6	100.8	2	2
9/2/2020 23:11	4	1.002	0	100	1	3
9/3/2020 9:35	4	-0.102	0	100	1	3
9/3/2020 9:39	4	-1.319	0	100	4	3
9/3/2020 9:49	4	-0.661	-0.1	99.9	4	3
9/3/2020 14:09	4	-3.817	-15.4	85.9	4	3
9/3/2020 20:34	4	-0.294	-33.2	69.6	4	3
9/3/2020 20:36	4	17.005	0	70.1	3	3
9/3/2020 21:21	4	17.305	13.1	82	3	3
9/3/2020 21:56	4	17.398	23.2	91.3	3	3
9/3/2020 21:57	4	15.347	23.4	91.5	2	3
9/3/2020 22:57	4	4.322	30.8	98.3	2	3
9/4/2020 0:32	4	2.248	34.9	102.1	2	3
9/4/2020 0:34	4	0.622	0	100	1	4
9/4/2020 7:14	4	-0.103	0	100	1	4
9/4/2020 7:18	4	-0.716	0	100	4	4
9/4/2020 7:23	4	-1.818	-0.2	99.9	4	4
9/4/2020 12:23	4	-7.451	-7	93.6	4	4
9/4/2020 13:58	4	-3.936	-14.9	86.3	4	4
9/4/2020 18:48	4	-0.704	-26.9	75.3	4	4
9/4/2020 18:51	4	11.033	0	75.8	3	4
9/4/2020 19:16	4	17.268	7.3	82.5	3	4
9/4/2020 19:46	4	17.223	15.9	90.4	3	4
9/4/2020 19:51	4	16.994	17.2	91.6	2	4
9/4/2020 22:11	4	2.249	27.9	101.4	2	4
9/4/2020 22:12	4	-0.102	0	100	1	5
9/4/2020 23:52	4	-0.104	0	100	1	5
9/1/2020 0:01	7	-0.103	0	100	1	1
9/1/2020 12:56	7	-0.103	0	100	1	1
9/1/2020 13:26	7	-0.103	0	100	1	1
9/1/2020 13:26	7	-0.594	0	100	4	1
9/1/2020 13:31	7	-0.594	-0.1	100	4	1
9/1/2020 13:36	7	-0.654	-15	99.9	4	1
9/1/2020 20:31	7	0.02	-24.7	77.3	4	1
9/1/2020 20:33	7	16.927	0	77.8	3	1
9/1/2020 20:38	7	17.407	1.5	79.1	3	1
9/1/2020 20:58	7	17.331	7.3	84.4	3	1
9/1/2020 21:23	7	17.282	14.5	91.1	3	1
9/1/2020 21:27	7	17.284	15.9	92.4	3	1
9/1/2020 21:28	7	15.926	16.2	92.6	2	1
9/1/2020 23:23	7	2.269	25.6	101.3	2	1
9/1/2020 23:43	7	1.862	26.4	101.9	2	1
9/1/2020 23:44	7	-0.1	0	100	1	2

[Greg_Deckler]

@JoeJoe666 - En caso de que te lo perdiste, actualicé mi respuesta original con una solución DAX. Soluciones de dos columnas y de una sola columna.

[daxer-almighty]

Aquí está el código M para hacerlo:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZdbcqQwDEW3MsV34kjym62ksv9tjCw/iW0SOh80dMFBtnx1sT4/D4gfgB8EBP8ATsDj7TB8vINC0HwBfCDIrxxfbxcG6bTuKaNP+iNjo7kwZsNo/M6kgD9jQyhn5QIt/8ao4g4j6NFAASWIjPJ89l7pO6zddCqSLxNjKPBJ76BQIa8MJAhVGqKPMr8dZgdMy3jz2IJRZovhSbpjFNLc0Ei4iDfhmPMjJxdWUhjpPl4bJj8uC8+5ocylf7TkdB8nKXIpDll5HgFlmjvOtPmp4NJD5GR8iYt3nBl0PKmYGkGtwvAh4U+MAyG6+pkh8wIz1qS3U32tmF5fqMjqPq2hUiaOrj4DlGK9F/H6O8pNFP4CwzkYkajpJ9CWm14B1sUK0BS/Yqj5CtelCa0uQz7tMG07pn2aZ66TYG8xO2IiRkQRfITmHkuu1ZdTIYp7UK3nWl8zhydWLirylOtTwmX7WGP6hIpx2r2cqZRlHuaOayuHChYq1g3RRZGxJ3Kj/CXTKoxdQsrtqvwVY4aqdA6Xyp84NCc0TquAPmtYkhfsHkwKGUqa8qdMa1Gxi8rdgfUmawSg1rXvMp4hNmDskBYItQBi/jvKDqF0lJXNQ4zZfrdc+0xYlrHPnMmcLfqYOeqcUZrkoaynGJrbd8w0D9bU5Sglqo2knvWi8I6rS8BLTrO0zIT4q90vNjpLJnTGo5vkuGLah48lHOQFSSZXOU4cb946x3ZlRcYpp1E3Sc2U7vbBIo6ir3fMKQyubXVmMJxmnBrIBX9pE+jtLWibHtkKdM0jQ9XlZiieOMiRXHqybHaomeoKMyMmCaqbFmiblhXXx8hTivUFx8VUJ45V3FwuyVF2G77IEZurLrkq443V2Ynhlbsw5ob51hT4vYa3TcEjpmzw/8A8agouzO+bgoI9bQpkU/+8KfDHC02BP15qCgr2tCko2OOmoHEPmwJ/vNYU+OO1piAn8XlTUHU8qZj3OV//AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, device = _t, m1 = _t, m2 = _t, m3 = _t, State = _t, Cycles = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type datetime}, {"device", Int64.Type}, {"m1", type number}, {"m2", type number}, {"m3", type number}, {"State", Int64.Type}, {"Cycles", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Cycles"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"device", Order.Ascending}, {"Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Time", "device", "m1", "m2", "m3", "State"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "PrevState",
        each
            let
                PrevRowState = List.SingleOrDefault(
                    Table.SelectRows(#"Reordered Columns",
                        (r) => r[Index] = [Index] - 1 and r[device] = [device])[State],
                    null
                )
            in
                PrevRowState
    ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ShouldAddOne",
        each
            if [PrevState] is null or ([PrevState] <> 1 and [State] = 1)
            then 1 else 0
    ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Cycle",
        each 
            // this is just a cumulative sum of ShouldAddOne
            List.Sum(
                Table.SelectRows(#"Added Custom1",
                    (r) => r[Index] <= [Index] and r[device] = [device]
                )[ShouldAddOne]
            )
    ),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"PrevState", "ShouldAddOne"})
in
    #"Removed Columns1"

[JoeJoe666]

@daxer todopoderoso Gracias. Sin embargo, no estoy seguro de cómo copiar pegar esto en M. Trataré de aprender y probarlo. La solución DAX funciona pero no funcionó para mí debido a más de 400.000 partidas individuales. ¿Esto me daría el mismo error de memoria?

[Greg_Deckler]

@JoeJoe666 - Aquí hay una mejora en el modelo que con suerte ayudará, me perdí un filtro. Déjame ver qué otros trucos puedo hacer para obtener un cálculo más optimizado pero prueba esto mientras tanto. Hay mucho que hacer con esta cosa, no es bonito lo que estás tratando de lograr. Para la solución Power Query, abra Advanced Editor y pegue el código, pero puede ser un poco peludo si nunca lo ha hecho antes. Además, el enfoque de 2 columnas debe ser mucho menos procesamiento.

Cycles Count Column = 
    VAR __BaseTable = 'Table'
    VAR __Time = [Time]
    VAR __device = [device]
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    FILTER(__BaseTable,[device]=__device && [time]<=__Time),
                    "__Previous",MAXX(FILTER(__BaseTable,[device]=__device && [time]<EARLIER([Time])),[Time])
                ),
                "__PreviousState",MAXX(FILTER(__BaseTable,[device]=__device && [Time]=[__Previous]),[State])
            ),
            "__CycleChange",IF(([State]=1 && [__PreviousState]<>1),1,0)
        )
RETURN
    SUMX(__Table,[__CycleChange])

[Greg_Deckler]

@JoeJoe666 - OK aquí hay otra mejora creo que debe funcionar mucho más rápido

Cycles Count Column = 
    VAR __Time = [Time]
    VAR __device = [device]
    VAR __WorkingTable = FILTER('Table',[device]=__device && [time]<=__Time)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    __WorkingTable,
                    "__Previous",MAXX(FILTER(__WorkingTable,[time]<EARLIER([Time])),[Time])
                ),
                "__PreviousState",MAXX(FILTER(__WorkingTable,[Time]=[__Previous]),[State])
            ),
            "__CycleChange",IF(([State]=1 && [__PreviousState]<>1),1,0)
        )
RETURN
    SUMX(__Table,[__CycleChange])

[JoeJoe666]

probó el enfoque de 2 columnas y la 1a columna ni siquiera regresa. mismo problema de memoria. 1 cosa, mis datos siempre vienen en orden cronológico para la columna de tiempo. ¿Sería menos procesamiento si no se requiere [time]<EARLIER[time]? mi mesa está pre-ordenada por tiempo y dispositivo

[Greg_Deckler]

@JoeJoe666- Bueno, podría intentar agregar un índice a la tabla en Power Query. Entonces usted podría utilizar esto:

1 columna:

Cycles Count Column 1 = 
    VAR __Index = [Index]
    VAR __device = [device]
    VAR __WorkingTable = FILTER('Table',[device]=__device && [Index]<=__Index)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    __WorkingTable,
                    "__Previous",MAXX(FILTER(__WorkingTable,[time]<EARLIER([Time])),[Time])
                ),
                "__PreviousState",MAXX(FILTER(__WorkingTable,[Time]=[__Previous]),[State])
            ),
            "__CycleChange",IF(([State]=1 && [__PreviousState]<>1),1,0)
        )
RETURN
    SUMX(__Table,[__CycleChange])

2 columnas:

Cycle Change Step 1 = 
    VAR __Table = FILTER('Table',[device]=EARLIER([device]) && [Index]<=EARLIER([Index]))
    VAR __Previous = MAXX(FILTER(__Table,[Index]<EARLIER([Index])),[Index])
    VAR __PreviousState = MAXX(FILTER(__Table,[Index]=__Previous),[State])
RETURN
    IF(([State]=1 && __PreviousState<>1),1,0)



Cycles 2 = SUMX(FILTER('Table',[device]=EARLIER([device]) && [Index]<=EARLIER([Index])),[Cycle Change Step 1])

Estoy trabajando a través de otro método.

[Greg_Deckler]

@JoeJoe666 - OK, aquí hay otro método para el proceso de 2 pasos que puede funcionar mejor (debe)

Cycle Change Step 1a = 
    VAR __device = [device]
    VAR __Index = [Index]
    VAR __Previous = CALCULATE(MAX([Index]),FILTER('Table',[device]=__device && [Index]<__Index))
    VAR __PreviousState = CALCULATE(MAX([State]),FILTER('Table',[Index]=__Previous))
RETURN
    IF(([State]=1 && __PreviousState<>1),1,0)


Cycles 2a = 
    VAR __device = [device]
    VAR __Index = [Index]
RETURN
    CALCULATE(SUM([Cycle Change Step 1a]), FILTER('Table',[device]=__device && [Index]<=__Index))

[Greg_Deckler]

@JoeJoe666 - Sólo haciendo un seguimiento, ¿alguna vez hizo esto operativo?

[Greg_Deckler · Accepted solution]

@JoeJoe666 - Ah, quieres Cthulhu - https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211

En realidad, no, no Cthulhu pero se puede hacer con 2 columnas, todavía estoy trabajando en la versión de una sola columna. PBIX se adjunta debajo de sig, es tabla (13).

Cycle Change = 
    VAR __Table = FILTER('Table (13)',[device]=EARLIER([device]) && [time]<=EARLIER([Time]))
    VAR __Previous = MAXX(FILTER(__Table,[time]<EARLIER([Time])),[Time])
    VAR __PreviousState = MAXX(FILTER(__Table,[Time]=__Previous),[State])
RETURN
    IF(([State]=1 && __PreviousState<>1),1,0)


Cycles = SUMX(FILTER('Table (13)',[device]=EARLIER([device]) && [Time]<=EARLIER([Time])),[Cycle Change])

OK, metí esto en una sola columna:

Single Column non working = 
    VAR __BaseTable = 'Table (13)'
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    FILTER(__BaseTable,[device]=EARLIER([device]) && [time]<=EARLIER([Time])),
                    "__Previous",MAXX(FILTER(__BaseTable,[time]<EARLIER([Time])),[Time])
                ),
                "__PreviousState",MAXX(FILTER(__BaseTable,[Time]=[__Previous]),[State])
            ),
            "__CycleChange",IF(([State]=1 && [__PreviousState]<>1),1,0)
        )
RETURN
    SUMX(__Table,[__CycleChange])

Se ha actualizado el PBIX.

[JoeJoe666]

Muchas gracias. No habría hecho nada de esto.

Mis datos tienen más de 400.000 filas. Intenté esto y Power BI sigue diciendo "trabajar en él" y luego 10-15 minutos más tarde dice "No hay suficiente memoria para completar esta operación. Por favor, intente esto más tarde..." Tengo 32 gb de memoria.