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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
zac_lim
New Member

Relationship between Channel Views and Programme

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 intervalViews per 5 min intervalProgramme ScheduleProgramme 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

6 REPLIES 6
Anonymous
Not applicable

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
Resident Rockstar
Resident Rockstar

Do u want create relationship between this two tables ?

 

if yes tell me the relationship

Channel to channel 

or 

Channel-Date to Channel-Date

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
Resident Rockstar
Resident Rockstar

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 .

 

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

 

 

> 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

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors