Aggregating Duration/Time

[samueltonks2712]

Hi there,

I'm trying to report on the number of activities for an individual who is part of a team. In January individual i was part of team 1 and had 2 activities. Unfortunately individual i didn't get on with team 1 and decided to move to team 2 where he had 5 activities. Each team has a unique id

In Power BI when I join individual i with the activities table to give me the total number of activies it quite rightly prodcues a count of 7. However the activities in different teams are independent and must not be aggregated.

My question is how do I report a change in team and thus two sets of count for individual i's activies in team 1 and tream 2 ? By my understanding Power BI only produces a snap shot in time, which causes a change in team to write over the origonal team and aggregate the data.

Is there some sort of measure or calculated field I could produce?

Any help would be greatly appreciated.

Many thanks,

Samuel

[v-micsh-msft · Accepted solution]

Hi samueltonks2712,

How about we add another column which used to mark the individual team join state? Let’s say if individual i changed from team 1 to team 2, then the team join state would be changed from (1, 0) to (0,1), then when we need to aggregate the activities, we could use the aggregate function with a team joinstate filter.

For a measure, this could be write in the following way:

Calculate(

             Sum(

                ‘TeamTable’[activitynum]), 

                 filter(all(‘TeamTable’), 

                   and(‘TeamTable’[joinstate]=1, ‘TeamTable’[individual]=i)

                     )

                )

In addition, if you would share us more details with your data model here, then we might provide a more suitable solution, regarding the aggregate measure or calculated column.

Regards