Forum Discussion

JoeJoe666's avatar
JoeJoe666
Frequent Visitor
5 years ago
Solved

Cycles Count

Hi, I am new to power BI. I am not sure if I need to use DAX or M for what I need. I would like create cycle count in new column based on the following: *for every device *change cycle when State...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    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's avatar
JoeJoe666
Frequent Visitor
5 years ago

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?

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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])

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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])

     

    • JoeJoe666's avatar
      JoeJoe666
      Frequent Visitor
      5 years ago

      tried the 2 column approach and 1st column not even coming back. same memory issue. 1 thing, my data always comes in chronological order for time column. Would it be less processing if [time]<EARLIER[time] is not required? my table is pre-sorted by time and device

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        5 years ago

        JoeJoe666 - Well, you could try adding an Index to the table in Power Query. Then you could use this:

        1 column:

         

        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 column:

         

        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])

         

        I'm working through another method.