Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 an...
Need Help
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

Hi, 

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

Mengyang