Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
samueltonks2712
Regular Visitor

Aggregating Duration/Time

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

1 ACCEPTED SOLUTION
v-micsh-msft
Microsoft Employee
Microsoft Employee

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

View solution in original post

1 REPLY 1
v-micsh-msft
Microsoft Employee
Microsoft Employee

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors