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.

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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