Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi all,
I have a table of about 2M records, with a column, task_starting_datetime (dd.mm.yyyy HH:mm:ss), which is my reference date. I need the specific time on the datetime, so it's hard to link it to a dimensional Calendar table.
What I'm trying to do is compute the median of the duration of the tasks for the last three months, so I used the following formula:
task key | task_starting_time | task_status | task_duration | median |
1 | 01/01/2023 18:32:00 | Completed | 2 | |
2 | 01/01/2023 19:39:00 | Completed | 3 | |
3 | 01/01/2023 22:31:00 | Completed | 4 | |
1 | 02/01/2023 09:21:00 | Completed | 1 | |
2 | 02/01/2023 19:21:00 | Completed | 3 | |
3 | 03/01/2023 05:55:00 | Completed | 5 | |
1 | 02/01/2023 22:44:20 | Failed | 6 | 1,5 |
2 | 02/01/2023 23:54:20 | Completed | 8 | 3 |
3 | 02/01/2023 23:58:20 | Completed | 7 | 5 |
Could someone help me out, please?
Thanks in advance for your help.
BR,
Sara
I have a table of about 2M records, with a column, task_starting_datetime (dd.mm.yyyy HH:mm:ss), which is my reference date. I need the specific time on the datetime, so it's hard to link it to a dimensional Calendar table.
Here's what you should do :
By splitting your "task_starting_datetime" in one column "dates" and another column "times" in Power Query Editor, it will make your dataset faster to load.
Then you can easily link your "task_starting_date" to the calendar/dates table.
Same for your "task_starting_time" linked to a "Times" table.
Btw,
TimeKey = Times[Hour]*10000+Times[Minute]*100+Times[Second]
It should make the calculations easier.
Hi @scaballerom
Is this a calculated column? If so, how about creating a measure and using it in a table visual?
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hi @scaballerom
Please try
MedianDurationTaskR3M =
VAR NumberOfDays = 91
VAR CurrentYear =
YEAR ( TODAY () )
VAR MaxDay =
MAX ( 'Fact Tasks'[task_starting_datetime] )
VAR MinDay = MaxDay - NumberOfDays
VAR Result =
CALCULATE (
MEDIAN ( 'Fact Tasks'[task_duration_min] ),
ALLEXCEPT ( 'Fact Tasks', 'Fact Tasks'[task_key] ),
YEAR ( 'Fact Tasks'[task_starting_datetime] ) = CurrentYear,
'Fact Tasks'[task_status] = "Completed",
ISBLANK ( 'Fact Tasks'[task_starting_datetime] ) = FALSE,
'Fact Tasks'[task_starting_datetime] <= MaxDay,
'Fact Tasks'[task_starting_datetime] > MinDay
)
RETURN
Result
@scaballerom
Hi Sara,
Not sure why you're filtering ALL( 'Table' ) then filtering for the SELECTEDVALUE('Fact Tasks'[task_key]). It seems to me that this way you will end up with the original table that was there in the original filter context. Am I missing something? Please try one of the following
MedianDurationTaskR3M =
VAR NumberOfDays = 91
VAR CurrentYear =
YEAR ( TODAY () )
VAR MaxDay =
MAX ( 'Fact Tasks'[task_starting_datetime] )
VAR MinDay = MaxDay - NumberOfDays
VAR FilterContext =
FILTER (
'Fact Tasks',
YEAR ( 'Fact Tasks'[task_starting_datetime] ) = CurrentYear
&& 'Fact Tasks'[task_status] = "Completed"
&& ISBLANK ( 'Fact Tasks'[task_starting_datetime] ) = FALSE
&& 'Fact Tasks'[task_starting_datetime] <= MaxDay
&& 'Fact Tasks'[task_starting_datetime] > MinDay
)
VAR Result =
CALCULATE ( MEDIAN ( 'Fact Tasks'[task_duration_min] ), FilterContext )
RETURN
Result
MedianDurationTaskR3M =
VAR NumberOfDays = 91
VAR CurrentYear =
YEAR ( TODAY () )
VAR MaxDay =
MAX ( 'Fact Tasks'[task_starting_datetime] )
VAR MinDay = MaxDay - NumberOfDays
VAR FilterContext =
FILTER (
CALCULATETABLE (
'Fact Tasks',
ALLEXCEPT ( 'Fact Tasks', 'Fact Tasks'[task_key] )
),
YEAR ( 'Fact Tasks'[task_starting_datetime] ) = CurrentYear
&& 'Fact Tasks'[task_status] = "Completed"
&& ISBLANK ( 'Fact Tasks'[task_starting_datetime] ) = FALSE
&& 'Fact Tasks'[task_starting_datetime] <= MaxDay
&& 'Fact Tasks'[task_starting_datetime] > MinDay
)
VAR Result =
CALCULATE ( MEDIAN ( 'Fact Tasks'[task_duration_min] ), FilterContext )
RETURN
Result
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
9 | |
8 |