Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculation over rows with condition

Hello all.   I am trying to improve a file I have worked on. I need some calculations over rows with a condition, and I was not able to do them on the interface (DAX), so I created different tables...
General Comment
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous,

     

    If you want to get the summaried stop time you can try to add new measure with below formula:

    Stop time(Summaried) =
VAR temp =
    ADDCOLUMNS (
        ALL ( Trips ),
        "Stoped Time", LOOKUPVALUE (
            Trips[TripEndDateTime],
            Trips[VehicleID], [VehicleID],
            Trips[Date], [Date],
            Trips[Index], [Index] - 1
        )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            FILTER ( temp, [VehicleID] = MAX ( [VehicleID] ) && [Date] = MAX ( [Date] ) ),
            "Diff", DATEDIFF ( [Stoped Time], [TripStartDateTime], SECOND )
        ),
        [Diff]
    )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Anonymous,

 

Can you please share pbix/excel file with some fake data? I will test on it.(it is trouble to manual input your data from the screenshots)

 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    You can take a look at below formulas if they suitable for your requirement.

     

    Measures:

    Number of Stop = 
CALCULATE (
    COUNT ( [VehicleID] ) - 1,
    FILTER (
        ALLSELECTED ( 'Trips' ),
        [VehicleID] = MAX ( [VehicleID] )
            && [TripStartDate] = MAX ( [TripStartDate] )
            && [Date] = MAX ( [Date] )
    )
)

Stop time = 
var _rangeEnd=MAX(Trips[TripStartDateTime])
VAR started =
    CALCULATE (
        MAX ( [TripEndDateTime] ),
        FILTER (
            ALL ( Trips ),
            [VehicleID] = MAX( [VehicleID] )
                && [Date] = MAX ( [Date] )
                && [TripEndDateTime] < _rangeEnd
        )
    )
RETURN
    DATEDIFF ( started,_rangeEnd, SECOND ) + 0

Return time = 
VAR _lastStart =
    CALCULATE (
        MAX ( Trips[TripStartDateTime] ),
        FILTER (
            ALLSELECTED ( Trips ),
            [VehicleID] = MAX ( [VehicleID] )
                && [Date] = MAX ( Trips[Date] )
        )
    )
VAR _lastEnd =
    CALCULATE (
        MAX ( Trips[TripEndDateTime] ),
        FILTER (
            ALLSELECTED ( Trips ),
            [VehicleID] = MAX ( [VehicleID] )
                && [Date] = MAX ( Trips[Date] )
        )
    )
RETURN
    IF (
        DATEVALUE ( _lastStart ) <> DATEVALUE ( _lastEnd ),
        DATEDIFF ( _lastStart, _lastEnd, SECOND ),
        DATEDIFF ( _lastEnd, DATEVALUE ( _lastEnd ) + 1, SECOND )
    )

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Anonymous

       

      That's impressive. I dindn't know that logic for grouping with criteria. That's awesome :smileysurprised:

       

      For Number of Stops and Return Time, it works perfectly, as it grabs one value only.

       

      However, for the Stop Time, it doesn't, because the calculation should somehow group (sum) all the partial stop times for each trip. With the Measure you proposed, I think we only get one value.

       

       

      So, for each trip we should do the time difference between the connected values (diagonally) and sum them,

       

      Is it possible to calculate the total Stop Time?

       

      Thanks again, this is something I'll definitely use on future projects.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Anonymous,

         

        If you want to get the summaried stop time you can try to add new measure with below formula:

        Stop time(Summaried) =
VAR temp =
    ADDCOLUMNS (
        ALL ( Trips ),
        "Stoped Time", LOOKUPVALUE (
            Trips[TripEndDateTime],
            Trips[VehicleID], [VehicleID],
            Trips[Date], [Date],
            Trips[Index], [Index] - 1
        )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            FILTER ( temp, [VehicleID] = MAX ( [VehicleID] ) && [Date] = MAX ( [Date] ) ),
            "Diff", DATEDIFF ( [Stoped Time], [TripStartDateTime], SECOND )
        ),
        [Diff]
    )

         

        Regards,

        Xiaoxin Sheng