Forum Discussion
Cycles Count
- 5 years ago
JoeJoe666 - Ah, you want Cthulhu - https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211
Actually, no, not Cthulhu but you can do it with 2 columns, I am still working on the single column version. PBIX is attached below sig, it is Table (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, got this into a single column:
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])Updated the PBIX.
JoeJoe666 - Ah, you want Cthulhu - https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211
Actually, no, not Cthulhu but you can do it with 2 columns, I am still working on the single column version. PBIX is attached below sig, it is Table (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, got this into a single column:
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])
Updated the PBIX.
Greg_Deckler . Thank you so much. I would not have firgured this out at all.
My data has over 400,000 rows. I tried this and Power BI keeps saying "working on it" then 10-15 mins later says "There's not enough memory to complete this operation. Please try this later...." I have 32gb of memory.