Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DaxSkeleton
Regular Visitor

Create Median measure for Counting Absent Data as 0

I am having trouble calculating the median number of a column, where each row is a different hour of a day, but there may be missing dates/hours that I'd like to count as 0 in my median function.

 

It's difficult to explain, so I put a table demonstrating my issue below, covering 5 days of the year (1/1/2023-1/5/2023). NumStudies is the number of studies done during the given date/time. I would like to calculate for the given timeframe (1/1-1/5 in this case), what is the median number of studies done for every hour of the day? If there is no date/time in the table (for example on 1/1 there were no other studies except at 1:00 pm and 8:00 pm) I would like that to count as 0 when I calculate the median studies done for each hour for the given time frame. 

 

Source Data:

DateTimeNumStudies
1/1/23 1:00 PM  1
1/2/23 1:00 PM  3
1/3/23 1:00 PM  5
1/5/23 1:00 PM  7
1/3/23 2:00 PM  1
1/4/23 2:00 PM  2
1/5/23 2:00 PM  3
1/5/23 3:00 PM  5
1/5/23 4:00 PM  10
1/5/23 5:00 PM  20
1/5/23 6:00 PM  30
1/5/23 7:00 PM  40
1/1/23 8:00 PM  100
1/2/23 8:00 PM  110
1/3/23 8:00 PM  120
1/4/23 8:00 PM  130
1/5/23 8:00 PM  140

 

What I'd like is a measure which calculates the median of the number of studies for each hour, accounting for date/times that may be missing. The results of the median function in this example for 1/1/2023 through 1/5/2023 would show:

 

Results:

HourMedianNumStudies
12:00 AM  0 
1:00 AM  0
2:00 AM  0
3:00 AM  0
4:00 AM  0
5:00 AM  0
6:00 AM  0
7:00 AM  0
8:00 AM  0
9:00 AM  0
10:00 AM  0
11:00 AM  0
12:00 PM  0
1:00 PM  3 (i.e. median of 1,3,5,0 (from 1/4/23) ,7)
2:00 PM  1 (median of 1,2,3,0,0)
3:00 PM  0
4:00 PM  0
5:00 PM   0
6:00 PM  0
7:00 PM  0
8:00 PM  120
9:00 PM  0
10:00 PM  0
11:00 PM  0

 

I've tried a number of different methods but have not been able to come up with anything that works. Any help is much appreciated.

 

Thanks in advance! 

0 REPLIES 0

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors