How to count rows with row calculation?

[Anonymous]

Hey guys, 

I want to count accumulated number of rows in this situation: 

When RELAY = ON and Temperature goes down by minimum 2 degrees F until Temperature starts rising in temperature again and RELAY = OFF, 

take an example from table is rows in red here: 

Date	Time	Temperature	Mode	Relay
5/8/2019	11:01:32	73.24	C	ON
5/8/2019	11:02:02	62.44	C	ON
5/8/2019	11:02:32	58.67	C	ON
5/8/2019	11:03:02	58.18	C	ON
5/8/2019	11:03:32	57.53	C	ON
5/8/2019	11:04:02	56.9	C	ON
5/8/2019	11:04:32	56.45	C	ON
5/8/2019	11:05:02	59.75	C	OFF

How I can write a query in new measure?

Thank you, 

Mengyang

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a measure as below.

Measure = 
VAR a =
    ADDCOLUMNS (
        'Table',
        "a",
        VAR time1 = 'Table'[Time]
        VAR time2 =
            CALCULATE (
                MAX ( 'Table'[Time] ),
                FILTER (
                    'Table',
                    'Table'[Date ] = EARLIER ( 'Table'[Date ] )
                        && 'Table'[Time] < time1
                )
            )
        VAR pre =
            CALCULATE (
                MAX ( 'Table'[Temperature] ),
                FILTER (
                    'Table',
                    'Table'[Time] = time2
                        && 'Table'[Date ] = EARLIER ( 'Table'[Date ] )
                )
            )
        RETURN
            IF ( pre <> BLANK () && pre - 'Table'[Temperature] > 1, 1, BLANK () )
    )
RETURN
    COUNTROWS ( FILTER ( a, [a] = 1 ) )

For more details, please check the pbix as attached.

[Anonymous]

Thank you, this solution meets most of needs, 

but do you know why I get error "can not find table "ON"" when I add this as another condition in IF? 

Mengyang

[Nathaniel_C]

Hi @Anonymous ,
Try this: Create two calculated columns

Previous temp = 

var _currTime = myTable[Time]
var _prevTime = CALCULATE(MAX(myTable[Time]),ALLEXCEPT(myTable,myTable[Time]),myTable[Time]<_currTime)
var _currTemp = myTable[Temperature]
var _prevTemp = CALCULATE(MAX(myTable[Temperature]),ALLEXCEPT(myTable,myTable[Time]),myTable[Time] = _prevTime)

return _prevTemp

Count = IF(myTable[Relay] = "on" && myTable[Previous temp]-myTable[Temperature]>=2,1,0)

Create the measure:

Countrows = 
sum(myTable[Count]

)

============================================== 

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anonymous]

Hi, 

thank you but I got an error "token eof expected" when creating columns "Previous Temp" and "count" by editing query - custom columns. 

Mengyang