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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous

     

    Again, impressive and straight to the point. It works flawlessly!

     

    Anyway, I didn't get one bit of the code:

     

    SUMX (
        ADDCOLUMNS (
            FILTER ( temp, [VehicleID] = MAX ( [VehicleID] ) && [Date] = MAX ( [Date] ) ),
            "Diff", DATEDIFF ( [Stoped Time], [TripStartDateTime], SECOND )
        ),
        [Diff]
    )

     If we filter according to Vehicle ID and Date, which makes perfect sense, I would expect the first "Stopped Value" to be wrong, as it comes from the previous entry (i.e. a different Date or Vehicle), but it doesn't happen to be like this, which is good for our purpose, but I don't get it. Let me show as if we did it in a table ourselves:

     

    VehicleID TripStartDateTime TripEndDateTime Index Stopped Time

    258502/10/2017 12:20:3902/10/2017 12:23:041 NA
    258502/10/2017 13:07:2502/10/2017 13:13:36202/10/2017 12:23:04
    258503/10/2017 12:31:0903/10/2017 12:37:25302/10/2017 13:13:36
    258503/10/2017 12:41:0103/10/2017 12:44:56403/10/2017 12:37:25
    258503/10/2017 12:56:0303/10/2017 13:01:31503/10/2017 12:44:56
    258503/10/2017 13:02:5203/10/2017 13:07:28603/10/2017 13:01:31
    381502/10/2017 12:20:0002/10/2017 12:24:17703/10/2017 13:07:28
    381502/10/2017 12:26:4002/10/2017 12:34:41802/10/2017 12:24:17
    381502/10/2017 12:36:5602/10/2017 12:44:22902/10/2017 12:34:41
    381502/10/2017 13:04:3702/10/2017 13:12:541002/10/2017 12:44:22

     

    To finalize this topic, where am I failing on the logic?

     

    Thanks again! :smileyhappy:

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    >> I would expect the first "Stopped Value" to be wrong, as it comes from the previous entry (i.e. a different Date or Vehicle), but it doesn't happen to be like this, which is good for our purpose, but I don't get it.

    Actually, you can use current date as filter condition to simply avoid this situation.

     

    The bolded part is the conditions, I used current vehicle ID and current date as the filter.(Some of summary functions can used to get current row contents)

     

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Xiaoxin Sheng

    I am new to BI . I have simlar requirement wherein need ot get ATM idletime in a column against ATM Machine wherein no deposit or withdrawal hapened during operations time . For example 1:00 Am to 4 AM if there  is no deposit or withdrawal then the column is populated with no. of hours smachine remained idle i.e no cash movement 

    Also I need a measure to get number of all such idle machines . 

     

    Can you help for both requirements ?

     

     Pls see the image