Forum Discussion
How to count rows with row calculation?
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
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.
4 Replies
Hi Anonymous ,
Try this: Create two calculated columnsPrevious 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 _prevTempCount = 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.
NathanielHi 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.
