Forum Discussion
Calculate unique viewership duration
hi, I have that captures viership of media/video content like netflix shown in table below. Row 1 captures viewership of customer "123456" watching video "Short Movie 2" that is 240 secs in durat...
v-juanli-msft
Community Support
Community SupportHi Anonymous
Create a date table
Date1 = ADDCOLUMNS(CALENDARAUTO(),"year-month",FORMAT([Date],"yyyy-mm"))
Add column in Table5,
month = FORMAT([Date],"yyyy-mm")Measures
s_Min =
CALCULATE (
MIN ( 'Table 5'[Start Index (secs)] ),
FILTER (
ALLSELECTED ( 'Table 5' ),
'Table 5'[Customer ID] = MAX ( 'Table 5'[Customer ID] )
&& 'Table 5'[Video Name] = MAX ( 'Table 5'[Video Name] )
&& 'Table 5'[month] = MAX ( 'Table 5'[month] )
)
)
s_Max =
CALCULATE (
MAX ( 'Table 5'[Start Index (secs)] ),
FILTER (
ALLSELECTED ( 'Table 5' ),
'Table 5'[Customer ID] = MAX ( 'Table 5'[Customer ID] )
&& 'Table 5'[Video Name] = MAX ( 'Table 5'[Video Name] )
&& 'Table 5'[month] = MAX ( 'Table 5'[month] )
)
)
e_Min =
CALCULATE (
MIN ( 'Table 5'[Stop Index (secs)] ),
FILTER (
ALLSELECTED ( 'Table 5' ),
'Table 5'[Customer ID] = MAX ( 'Table 5'[Customer ID] )
&& 'Table 5'[Video Name] = MAX ( 'Table 5'[Video Name] )
&& 'Table 5'[month] = MAX ( 'Table 5'[month] )
)
)
e_Max =
CALCULATE (
MAX ( 'Table 5'[Stop Index (secs)] ),
FILTER (
ALLSELECTED ( 'Table 5' ),
'Table 5'[Customer ID] = MAX ( 'Table 5'[Customer ID] )
&& 'Table 5'[Video Name] = MAX ( 'Table 5'[Video Name] )
&& 'Table 5'[month] = MAX ( 'Table 5'[month] )
)
)
sec total = IF([s_Max]<=[e_Min],[e_Max]-[s_Min],[e_Min]-[s_Min]+[e_Max]-[s_Max])
duration_f =
IF (
[sec total] <> BLANK (),
IF (
[s_Max] <= [e_Min],
[s_Min] & "-" & [e_Max],
[s_Min] & "-" & [e_Min] & "," & [s_Max] & "-" & [e_Max]
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User
Super UserI thought about it some more and here is what I would do:
For each customer and video collect all the viewed seconds ( via GENERATESERIES ) into a table variable and then do a DISTINCTCOUNT on the second values. That has the same effect as EXCEPT but across all video fragments.