Forum Discussion
Cycles Count
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.
Here's the M code to do it:
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"daxer-almighty Thank you. However, I am not sure how to even copy paste this into M. I will try to learn and try it. The DAX solution works but didn't work for me due to 400,000+ line items. Would this give me the same memory error?
JoeJoe666 - Here is one improvement to the model that will hopefully help, I missed a filter. Let me see what other tricks I can do to get a more optimized calculation but try this in the mean time. There is a lot going on with this thing, it isn't pretty what you are trying to accomplish. For the Power Query solution, you open up Advanced Editor and paste in the code but it can be a little hairy if you have never done it before. Also, 2 column approach should be a lot less processing.
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])JoeJoe666 - OK here is another improvement I think that should run much faster
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])
