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.
Hello all,
I am pretty new on Power BI and i am working on a report where i need to aggregate data based on some specific conditions:
Here is an example of what i am trying to achive:
Conditions are
Aggregate/group on continous data (based on timestamp) with
Start > 90 to End < 20 (taking the lowest End value)
If there are increment ignore (as last case shown on image)
Hope that is clear enough.
I have tryied different approach with DAX expression, Visuals, M Query, with descrete results but not exactly what i am looking for. I have tried to find something similar on this Forum but unsuccessfully, i start to think that maybe is not feasable as i want, and i need to change approach.
Do you have suggestions?
Thanks!
Edit 3rd June: Adding sample data:
INPUT
Item | Timestamp | Start | End | Duration |
AAA | 22/05/2021 10:00 | 99 | 56 | 4h |
AAA | 22/05/2021 14:00 | 54 | 32 | 2h |
AAA | 23/05/2021 01:00 | 32 | 7 | 1h |
AAA | 24/05/2021 09:00 | 95 | 17 | 5h |
AAA | 25/05/2021 11:00 | 83 | 45 | 3h |
AAA | 25/05/2021 13:00 | 44 | 6 | 3h |
AAA | 25/05/2021 17:00 | 91 | 38 | 4h |
AAA | 25/05/2021 18:00 | 30 | 14 | 1h |
BBB | 22/05/2021 18:00 | 75 | 45 | 2h |
BBB | 23/05/2021 08:00 | 93 | 23 | 4h |
BBB | 26/05/2021 12:00 | 22 | 4 | 1h |
BBB | 27/05/2021 12:00 | 94 | 28 | 3h |
CCC | 19/05/2021 12:00 | 96 | 58 | 2h |
CCC | 20/05/2021 10:00 | 58 | 10 | 2h |
CCC | 20/05/2021 13:00 | 5 | 4 | 1h |
CCC | 24/05/2021 12:00 | 96 | 58 | 2h |
CCC | 25/05/2021 10:00 | 58 | 78 | 0h |
CCC | 25/05/2021 13:00 | 74 | 12 | 3h |
OUTPUT
Item | Timestamp | Start | End | Duration |
AAA | 22/05/2021 10:00 | 99 | 7 | 7h |
AAA | 25/05/2021 17:00 | 91 | 14 | 5h |
AAA | 24/05/2021 09:00 | 95 | 17 | 5h |
BBB | 23/05/2021 08:00 | 93 | 4 | 5h |
CCC | 19/05/2021 12:00 | 96 | 4 | 5h |
Solved! Go to Solution.
Here is a workaround for you, please see the attachment below
Proud to be a Super User!
I am confused with your logic
Proud to be a Super User!
Hello ryan,
The previous row start from 95 and end at 17, while next one start from 83 that is greater of previous end (17).
Raw data are ordered by item and by timestamp. Ideally every "process" start from 100 and end at 0.
I would like to isolate and aggregate this "process" accordingly to reqs i wrote in my main post.
Hope that is more clear now.
Thanks!
Here is a workaround for you, please see the attachment below
Proud to be a Super User!
Thanks @ryan_mayu it does exactly what i needed! My only concern is regarding the computational load. My full data table size is about 30k/40k rows.
Do you think there could be problem?
Thanks!
I am not sure about that, you can have a try. If there is a performance issue, maybe you can ask for help on the community again to see if anyone else can provide better solution.
Proud to be a Super User!
Please provide sample data in usable format (not as a picture) .
Thanks Ibendlin, i am going to add sample data in main post
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |