topic Re: Calculate total time someone spent playing a game in DAX Commands and Tips

Calculate total time someone spent playing a game

sknnyftn — Sun, 08 Dec 2024 20:36:36 GMT

I am trying to calculate the total time someone has spent playing a particular poker game. The problem I have is that they may be playing multiple games at the same time, or just one, and I have a simple table with chronological entry and exit times as shown below and in the pbx file. In cases where multiples are going at the same time, I don't have a preference on first in first out or last in first out methodology.

I have tried splitting the data out into seperate entry and exit tables using CALCULATETABLE, attempted to add an index column to both with ROWNUMBER, and develop a measure to using DATEDIFF between the two tables, with no luck. I am at a loss on how to get this done at all and would really appreciate any help.

Game Data.pbx

Date/Time	Game	Type
12/07/24 6:14 PM	Texas Hold'em 0.25/0.5	Game Exit
12/07/24 3:36 PM	Texas Hold'em 0.25/0.5	Game Enter
12/07/24 12:28 PM	Texas Hold'em 0.25/0.5	Game Exit
12/07/24 10:56 AM	Texas Hold'em 0.25/0.5	Game Enter
12/07/24 12:22 AM	Texas Hold'em 0.25/0.5	Game Exit
12/07/24 12:21 AM	Texas Hold'em 0.25/0.5	Game Exit
12/06/24 9:07 PM	Texas Hold'em 0.25/0.5	Game Enter
12/06/24 7:05 PM	Texas Hold'em 0.25/0.5	Game Enter
12/06/24 1:02 PM	Texas Hold'em 0.25/0.5	Game Exit
12/06/24 11:32 AM	Texas Hold'em 0.25/0.5	Game Enter

Re: Calculate total time someone spent playing a game

OwenAuger — Sun, 08 Dec 2024 21:51:58 GMT

Hi sknnyftn

Just confirming the exact requirements before suggesting a solution:

Do you want to calculate the total time for which at least one game is "active"?
For example, two games active simultaneously for 5 minutes would give a result of 5 minutes?
Alternatively, do you want to calculate the total time for each game separately and sum?
For example, two games active simultaneously for 5 minutes would give a result of 10 minutes?

Here's a plot of "Active Games" based on your sample data.

Active Games = # games entered so far less # games exited so far.

Option 1 above would measure the area under the minimum of the blue line and dotted red line.
Option 2 above would measure the area under the blue line.

Re: Calculate total time someone spent playing a game

sknnyftn — Mon, 09 Dec 2024 00:07:59 GMT

Owen, I am looking to accomplish option 2 and find area under the blue line.

Re: Calculate total time someone spent playing a game

OwenAuger — Mon, 09 Dec 2024 17:30:03 GMT

Thanks for that sknnyftn 🙂

I have uploaded a PBIX with one possible solution.

I added a Date table. This table is disconnected from Data, but the measure created below takes into account the min/max Date filtered and determines the Game Duration intersecting that date range.
The Game Duration measures are shown below:

Game Duration (days) = VAR MinDateTime = MIN ( 'Date'[Date] ) VAR MaxDateTime = MAX ( 'Date'[Date] ) + 1 -- Add 1 to represent exactly 0:00 at the end of MaxDate VAR Result = SUMX ( Data, VAR CurrentType = Data[Type] VAR Multiplier = SWITCH ( CurrentType, "Game Enter", 1, "Game Exit", -1 ) VAR EffectiveDateTime = MIN ( MAX ( Data[Date/Time], MinDateTime ), MaxDateTime ) VAR Contribution = ( MaxDateTime - EffectiveDateTime ) * Multiplier RETURN Contribution ) RETURN Result

Game Duration (hours) = [Game Duration (days)] * 24

Game Duration (minutes) = [Game Duration (days)] * ( 24 * 60 )

This appears to give the correct result based on your sample data. Does it work as expected at your end?

Regards

Re: Calculate total time someone spent playing a game

sknnyftn — Mon, 09 Dec 2024 21:28:40 GMT

Owen, your solution appears to work perfectly. I tried with slightly different data and it tracked as expected. Thank you for the help.

Re: Calculate total time someone spent playing a game

sknnyftn — Tue, 10 Dec 2024 02:07:04 GMT

Is there a straightforward way to switch between options 1 and 2?

Re: Calculate total time someone spent playing a game

OwenAuger — Tue, 10 Dec 2024 11:02:28 GMT

sknnyftn

Excellent question!

I gave this some further thought, and we can write the measures differently in a way that makes switching between methods more straightforward.

I've attached an updated PBIX.

The idea is:

Construct a list of distinct Date/Time values (Entry & Exit).
For each Date/Time
1. Determine the "next" Date/Time (I used the OFFSET function for this).
2. Compute the number of "active games" (cumulative Entries less Exits).
For each Date/Time, multiply the time interval (adjusted to be within the filtered date range) by either:
1. Option 1: 1 if there is at least one active game; or
2. Option 2: The number of active games.

Below are the measures involved.

In the two final Game Duration measures, the only difference is the CountOverlaps variable.

TRUE corresponds to Option 2 and FALSE corresponds to Option 1.

Entry Count = CALCULATE ( COUNTROWS ( Data ), KEEPFILTERS ( Data[Type] = "Game Enter" ) )

Exit Count = CALCULATE ( COUNTROWS ( Data ), KEEPFILTERS ( Data[Type] = "Game Exit" ) )

Entry Count Cumulative = VAR MaxDateTime = MAX ( Data[Date/Time] ) VAR Result = CALCULATE ( [Entry Count], Data[Date/Time] <= MaxDateTime ) RETURN Result

Exit Count Cumulative = VAR MaxDateTime = MAX ( Data[Date/Time] ) VAR Result = CALCULATE ( [Exit Count], Data[Date/Time] <= MaxDateTime ) RETURN Result

Active Games = [Entry Count Cumulative] - [Exit Count Cumulative]

Game Duration (days) = -- Option 2 VAR CountOverlaps = TRUE VAR MinDateTime = MIN ( 'Date'[Date] ) VAR MaxDateTime = MAX ( 'Date'[Date] ) + 1 -- Add 1 to represent exactly 0:00 at the end of MaxDate VAR DateTimes = VALUES ( Data[Date/Time] ) VAR Result = SUMX ( DateTimes, VAR NextDateTime = OFFSET ( 1, DateTimes, ORDERBY ( Data[Date/Time] ) ) VAR NextDateTimeNoBlank = COALESCE ( NextDateTime, MaxDateTime ) -- convert blank NextDateTime to MaxDateTime VAR EffectiveDateTimeStart = MIN ( MAX ( Data[Date/Time], MinDateTime ), MaxDateTime ) VAR EffectiveDateTimeEnd = MIN ( MAX ( NextDateTimeNoBlank, MinDateTime ), MaxDateTime ) VAR ActiveGames = [Active Games] VAR Multiplier = IF ( CountOverlaps, ActiveGames, ActiveGames >= 1 ) VAR Contribution = ( EffectiveDateTimeEnd - EffectiveDateTimeStart ) * Multiplier RETURN Contribution ) RETURN Result

Game Duration non-duplicated (days) = -- Option 1 VAR CountOverlaps = FALSE VAR MinDateTime = MIN ( 'Date'[Date] ) VAR MaxDateTime = MAX ( 'Date'[Date] ) + 1 -- Add 1 to represent exactly 0:00 at the end of MaxDate VAR DateTimes = VALUES ( Data[Date/Time] ) VAR Result = SUMX ( DateTimes, VAR NextDateTime = OFFSET ( 1, DateTimes, ORDERBY ( Data[Date/Time] ) ) VAR NextDateTimeNoBlank = COALESCE ( NextDateTime, MaxDateTime ) -- convert blank NextDateTime to MaxDateTime VAR EffectiveDateTimeStart = MIN ( MAX ( Data[Date/Time], MinDateTime ), MaxDateTime ) VAR EffectiveDateTimeEnd = MIN ( MAX ( NextDateTimeNoBlank, MinDateTime ), MaxDateTime ) VAR ActiveGames = [Active Games] VAR Multiplier = IF ( CountOverlaps, ActiveGames, ActiveGames >= 1 ) VAR Contribution = ( EffectiveDateTimeEnd - EffectiveDateTimeStart ) * Multiplier RETURN Contribution ) RETURN Result

Does this work for you?

This likely requires some testing to confirm performance is acceptable.

Regards

Re: Calculate total time someone spent playing a game

abuislam — Wed, 11 Dec 2024 07:10:54 GMT

To calculate the total time someone spent playing a game with overlapping sessions, you can try sorting the data by date/time and calculating the time difference between entry and exit using DATEDIFF. You can sum these differences, keeping in mind overlapping sessions by ensuring you account for any concurrent games. If you're using Power BI, the EARLIER function might help with comparing rows for accurate calculation.

By the way, if you're looking for a fun game to relax and unwind, Car Parking Multiplayer is a great option. You can find more details about it at https://modcarparking.com/.

Re: Calculate total time someone spent playing a game

sknnyftn — Fri, 20 Dec 2024 22:42:41 GMT

Sorry I got tied up on a different project and didn't respond sooner but this works perfectly.