Forum Discussion
Pie chart + measure?
- 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 😉
There 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 🙂
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. ☹️
- Sergii243 years ago
Super User
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 😉
- Kohrinn3 years ago
Helper I
Thank you! 😄
- Kohrinn3 years ago
Helper I
I tried it and I can't find the piece of code where "Idle" is added to each event automatically 😓
I've copied your caclulations and nothing happened ☹️
- Sergii243 years ago
Super User
Hi Kohrinn, before copy-paste code from PowerQuery try to better understand it. I haven't seen your data, so you should first understand the proposed logic in that example, then compare the initial table with your and perfrom modificaitons step-by-step. To help you further I would need more details on what you've tried, what do you struggle with and sample of your raw data. Let me know if any help is needed!