Relationship between Channel Views and Programme

[zac_lim]

Hi guys,

This was an exercise given several days ago. I could not find a good way to solve the relationship between these two tables.

Views per 5 min intervalProgramme Schedule

I would like to find information about the channel rankings (Popular overall and by channel) but the way the data was presented makes it hard to build a relationship between the two tables.

I used a very messy and inaccurate solution on excel. I cleaned up the data using R (subset the programme schedule by channels to decrease the dataset burden) and used an adjusted start time (round up to the nearest 5mins) as the basis of my relationship. I then used Vlookup to match the adjusted start times of the Programme and the Views at each interval.

It worked somewhat. However, the views were not too accurate. Channels with less shows but longer showtime were not getting counted often. While Channels with numerous shows but shorter showtime were getting counted more often. So I used average views per showing. However total views are inaccurate.

I'd like to know how you would tackle this problem. I have been stumped on this one for a some time

Regards

Zac

[Anonymous]

Hi @zac_lim,

As Baskar said, it is hard to create the relationship between these tables. In my opinion, you can use dax to get the specific value.

If the views means the current total views amount, you can try to use below formula if it suitable for your requirement:

Measure formula:

Views=
var currChangel=LastNoBlank(Table2[Channels],[Channels])
var CurrStartTime= Max(Table2[StartTime])
var CurrEndTime=Max(Table2[EndTime])
var temp=Filter(All(Table1),Table1[Channel]=currChangel)
return
Maxx(filter(temp,AND(Table1[Time Interval]>=CurrStartTime,Table1[Time Interval]>=CurrEndTime)),Table1[Views])

Regards,

Xiaoxin Sheng

[Baskar]

Do u want create relationship between this two tables ?

if yes tell me the relationship

Channel to channel 

or 

Channel-Date to Channel-Date

[zac_lim]

So for example:

On the second table,

On 1st september 2016, 13:12 - 13:22, one episode of the programme AALAM GUMBALL is showing.

On the 5mins interval table, under the CNArabia channel (you can't see it from the snapshot) for 1st September 2016, there will be channel views at period 13:15 and 13.20 (5min interval). At this point, this is the snapshot of channel views for AALAM GUMBALL 13:12 - 13:22.

Is there anyway to match these? or create a relationship?

I hope this is clear enough. Thanks

Zac

[Baskar]

Ya i get clearly what u want .

We can't create relationship like this case. bec relationship only works for matching cases am i right ?

Sorry dude .

[zac_lim]

Yeah, its impossible in its current state unless maybe we create a new table or clean the data. That's why I used an alternate solution but it was inelegant and tedious. I was wondering if anyone in the community might have a unique perspective to solving it.

Thanks though

[zac_lim]

> Do u want create relationship between this two tables ?

Yes, if possible.

> if yes tell me the relationship

Matching Channel views of 5 mins interval with the Programming Schedule. Each channel has many different programmes. Its not just the date but time as well, down to the minute. What you see here is just a snapshot. there are several thousand rows.

I need to find the Programme rankings. However the table only shows Channel Views at specfic time. I need it to somehow match the programming schedules so I can find the Programme rankings. It was an exercise introduced by a company I applied for. Maybe a relationship cannot be directly established and we may need to clean the table?

Thanks

Zac