Forum Discussion
Kohrinn
Helper I
3 years agoPie chart + measure?
Hi, I have a table, where I keep the start and end dates of Events and SubEvents. I assume, that Event starts when first SubEvent starts and ends when the last SubEvent ends. There are gaps between...
- 3 years ago
Take a closer look at *.pbix file. "Idle" subevent is automatically added to each event in PowerQuery, so you don't need to it manually 😉
Sergii24
Super User
3 years agoThere are 2 important steps:
- Add "Idle" as a subevent for each event (in the worst case its values will be 0)
- Create a dedicated "Duration" calculation for newly created "Idle" row
First we need to obtain this table in Power Query
Then you can use calculated column for the calculation of "idle" duration:
First we need to obtain total event time:
Duration of Event =
VAR _CurrentEvent = SELECTEDVALUE( Table_Events[Event] )
VAR _StartOfSelectedEvent =
MINX(
FILTER(
Table_Events,
Table_Events[Event] = _CurrentEvent
),
Table_Events[Start]
)
VAR _EndOfSelectedEvent =
MAXX(
FILTER(
Table_Events,
Table_Events[Event] = _CurrentEvent
),
Table_Events[End]
)
RETURN _EndOfSelectedEvent - _StartOfSelectedEvent
And then we can calculate duration of each of subevents:
Subevent Duration =
VAR _CurrentEvent = SELECTEDVALUE( Table_Events[Event] )
RETURN
IF(
Table_Events[Subevent] <> "Idle",
Table_Events[End] - Table_Events[Start],
Table_Events[Duration of Event] -
SUMX(
FILTER(
Table_Events,
Table_Events[Event] = _CurrentEvent
),
Table_Events[End] - Table_Events[Start]
)
)
I've uploaded a sample *.pbix file here. I'll keep it here for you for the next 20-30 days 🙂
- Kohrinn3 years ago
Helper I
It looks really promising - one question though. Is it any way around adding "Idle" subevent to every event? I have like hundreds of Events now and they are still coming so adding manually "Idle" is not a solution. ☹️