Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |