Solved: Recuento de ciclos - Page 2

JoeJoe666 · ‎09-05-2020

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 · ‎09-05-2020

@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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Recuento de ciclos

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024