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.
I need to calculate the time an animal spends in a sector, and only for the time it resides in that sector - an animal can move in and out of a sector many times. Each "visit" constitutes a new visit and the reset must apply. In the data below, you can see how animal 100A visited sector A, then moved to sector B, then moved back to sector A.
RodentId : Cage : Sector : Time : Seconds : Index
100A: XX: A: 15:20:21: 1 second: 1
100A: XX: A: 15:20:22: 1 second: 2
100A: XX: B: 15:20:23: 1 second: 3
100A: XX: B: 15:20:24: 1 second: 4
100A: XX: B: 15:20:25: 1 second: 5
100A: XX: A: 15:20:26: 1 second: 6
100A: XX: A: 15:20:27: 1 second: 7
Below is my current calculation, and the fact that sector is recurring is causing the "revisit" to sector A, not to reset the calculation and continues from the previous visit. How do I overcome this?
GroupPeriod =
SUMX (
FILTER (
'Sample data 1',
EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
&& EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
&& EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector] &&
&& EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
),
'Sample data 1'[Period]
)
Solved! Go to Solution.
Issue resolved for anyone interested. The issue was with the formula GroupMinTime. It required a statement to enforce a reset on RodentId.
GroupMinTime =
CALCULATE(
MAX(
'Sample data 1'[GroupVar])
,FILTER(all('Sample data 1')
,'Sample data 1'[Index] <= EARLIER('Sample data 1'[Index])
&& 'Sample data 1'[RodentId] = EARLIER('Sample data 1'[RodentId])
))
@Etienne123,
Create the following columns in your table.
Group = MINX(FILTER(ALL('Sample data 1'),'Sample data 1'[Sector]<>EARLIER('Sample data 1'[Sector])&&'Sample data 1'[Time]>EARLIER('Sample data 1'[Time])),'Sample data 1'[Time])
GroupPeriod = SUMX ( FILTER ( 'Sample data 1', EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId] && EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage] && EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector] && EARLIER('Sample data 1'[Group])='Sample data 1'[Group] && EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index] ), 'Sample data 1'[Seconds] )
Regards,
Lydia
Hi
I cannot get your suggestion to work. Note that in my dataset period constiture what you termed as "seconds" as it shows each 0.5 interval for each record.
Group =
MINX(
FILTER(
ALL('Sample data 1'),
'Sample data 1'[Sector]<>EARLIER('Sample data 1'[Sector])
&&'Sample data 1'[Time]>EARLIER('Sample data 1'[Time]))
,'Sample data 1'[Time])
And then
GroupPeriod =
SUMX (
FILTER (
'Sample data 1',
EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
&& EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
&& EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector]
&& EARLIER('Sample data 1'[Group]) = 'Sample data 1'[Group]
&& EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
), 'Sample data 1'[Period]
)
The Group formula do not group the cohort of visits to a sector together.
@Etienne123,
Please share the complete table that contains period column and post expected result here.
Regards,
Lydia
Let me try and explain. This is a visual solutio tied to IoT sensor measures in a cage measurting the movement of animals. Imagine a cage, with 3 animals, moving freely between 6 sectors in a cage. Each sector has an IoT device that takes a measure every 0.5 seconds. The business outcome is to measure movement and how long an animal spends in any one sector. The data literally looks like the images below (I am trying to calculate GroupPeriod - it cumulatively sum each 0.5 second measure until there is a change in Cage, Rodent (Animal), and Sector when it should revert back to 0.5 and start cumulatively summing again).
I have some formulae that works somewhat, but there is still something wrong as it does not work 100% as it does not revert back to 0.5 in all instances:
Image 1 - you can see that the same rodent, changed from sector 3 to 2 and the GroupPeriod correctly reverted back to 0.5
Image 2 - you can see that the same rodent, changed back from 2 to 3, but GroupPeriod continue the cumulatifve sum from the previous change (it hould have reverted back to 0.5, but continues with 21.5 seconds
Image 3 - you can see here how there is a change in rodent and so the GroupPeriod correctly reverted back to 0.5 seconds
Current formulae below the images:
Formulae:
GroupVar - creates a startying row for each series (I do not think this works correctly, but I cant figure out why)
GroupVar = VAR S1 = 'Sample data 1'[Sector] VAR S2 = CALCULATE(MIN('Sample data 1'[Sector]),FILTER('Sample data 1','Sample data 1'[Index] = EARLIER('Sample data 1'[Index])-1)) VAR S3 = if(S1<>S2,'Sample data 1'[Time],BLANK()) RETURN S3
GroupMinTime - fills the above valyue down within the series
GroupMinTime = CALCULATE(MAX('Sample data 1'[GroupVar]),FILTER(all('Sample data 1'),'Sample data 1'[Index] <= EARLIER('Sample data 1'[Index])))
Group - Creates a proper unique group to include Rodebnt, Sector, Cage and the above
Group = 'Sample data 1'[GroupMinTime] & 'Sample data 1'[rodentId] & 'Sample data 1'[Sector] & 'Sample data 1'[cage]
GroupPeriod - creates the cumulative sum
GroupPeriod =
SUMX (
FILTER (
'Sample data 1',
EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
&& EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
&& EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector]
&& EARLIER('Sample data 1'[Group]) = 'Sample data 1'[Group]
&& EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
), 'Sample data 1'[Period]
)
Any ideas greatly appreciated
Issue resolved for anyone interested. The issue was with the formula GroupMinTime. It required a statement to enforce a reset on RodentId.
GroupMinTime =
CALCULATE(
MAX(
'Sample data 1'[GroupVar])
,FILTER(all('Sample data 1')
,'Sample data 1'[Index] <= EARLIER('Sample data 1'[Index])
&& 'Sample data 1'[RodentId] = EARLIER('Sample data 1'[RodentId])
))
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |