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 have timestamps under each other in one column, have detected the rows where full time period starts and ends thanks to indexing. Now, how do I lopp through the column and sum the whole time in between all the start and end rows? See sample snapshot:
Solved! Go to Solution.
Hi @Anonymous
Based on my simplified sample data, the measure could get the datediff between the start and end time.
Measure = var last_start=CALCULATE(MAX('Time'[time]),FILTER(ALL('Time'),'Time'[time]<=MAX('Time'[time])&&'Time'[end/start]="start")) return IF(MAX('Time'[end/start])="end",DATEDIFF(last_start,MAX('Time'[time]),HOUR))
If you need column,you may try below dax.
Column = var last_start=CALCULATE(MAX('Time'[time]),FILTER('Time','Time'[time]<=EARLIER('Time'[time])&&'Time'[end/start]="start")) return IF('Time'[end/start]="end",DATEDIFF(last_start,'Time'[time],HOUR))
Regards,
The measure you have posted did not work for me, but I've used a different approach - adding new index column to the table and then merging the table on itself.
Hi @Anonymous
You may try to create a measure like below.
Measure = VAR last_start = CALCULATE ( MAX ( 'Time'[time] ), FILTER ( ALL ( 'Time' ), 'Time'[time] <= MAX ( 'Time'[time] ) && 'Time'[end/start] = "start" ) ) RETURN IF ( MAX ( 'Time'[end/start] ) = "end", DATEDIFF ( last_start, MAX ( 'Time'[time] ), HOUR ) )
Regards,
Thank you for the help. However, the solution does not work for me. How can you make this measure iterate over the whole table with more than one start-end period possible for every id? @v-cherch-msft
Because the column looks like this, with more specified periods for one id. @v-cherch-msft
The measure you've provided just does not do the job. I've separated the times into two columns and just need do substract them from each other. If there is end time provided, substract start time from it otherwise from now time substract start time. As in pictures provided.
Hi @Anonymous
If you have resolved the issue, please mark the right reply as answer, If not, please share more details for us so that we could help further on it.
Regards,
MORE DETAILS:
The problem I am solbing is to try to sum the time of full occupancy of a box. I have a table with measurements 3-6 per day per box. Single measurement is in a single row, with percent_calculted stating the level of occupancy of the box and measured_at_utc stating the time of measurement.
Based on this data, using indexed columns, I got the earlier values of percent_calculated, time and code into the next row as can be seen in picture below
Thanks to that, I've been able to determine which time is start time of full occupancy of a box and which is the end time (if code = added code & percent> added percent etc).
Hence, I have the start and end times determined and now I need to count the difference between them. The issue is that there are thousands of them with unknown number of blanks between the two values and I need to loop through all of them.
The desired result is time of full occupancy, so I can determine for example longest period of full occupancy, average for all boxes, average for box, max, min etc etc etc.
Thank you for your time @v-cherch-msft
Hi @Anonymous
Based on my simplified sample data, the measure could get the datediff between the start and end time.
Measure = var last_start=CALCULATE(MAX('Time'[time]),FILTER(ALL('Time'),'Time'[time]<=MAX('Time'[time])&&'Time'[end/start]="start")) return IF(MAX('Time'[end/start])="end",DATEDIFF(last_start,MAX('Time'[time]),HOUR))
If you need column,you may try below dax.
Column = var last_start=CALCULATE(MAX('Time'[time]),FILTER('Time','Time'[time]<=EARLIER('Time'[time])&&'Time'[end/start]="start")) return IF('Time'[end/start]="end",DATEDIFF(last_start,'Time'[time],HOUR))
Regards,
The measure you have posted did not work for me, but I've used a different approach - adding new index column to the table and then merging the table on itself.
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 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |