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.

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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

JoeJoe666 - OK, here is another method for the 2 step process that may perform better (should)

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's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    JoeJoe666 - Just following up, did you ever get this operational?