Forum Discussion
Helper Iindex/match or vlookup
As a TV provider, we get reports on viewership of specific channels on specific days. However, if someone watches a recording (DVR), it shows as channel 1000.
I am looking at creating a column that IF it sees it is channel 1000, it will search by the channel name and pull in the channel number, not to include channel 1000.
In the ex below, 327 is 5StarMax - and contains details for that day.
1000, below it - I would like to pull in that it is channel 327, in a new column (last column as of now).
- Hopefully this makes sense - Thank you!
mmoroni , A new column like
maxx(filter(table, [channel] =earlier([channel]) && [ch#] <> 100),[ch#])
or
if([ch#] =1000,maxx(filter(table, [channel] =earlier([channel]) && [ch#] <> 100),[ch#]), blank())
4 Replies
- amitchandak
Super User
mmoroniCh# currently shows 1000. I need a new column, [DVRch#]. If Ch#=1000, vlookup [channelname] and return value from [Ch#], excluding 1000
So in the case above, I want the 2nd line to search for the same channel name, and return the first value that is NOT 1000. IE, I want this one to return "327"
In the example above, i just wrote:DVR Ch# = if('Viewership_By_Day_-_Monthly'[Type]="DVR", "True") just for a starting point - but it means nothing.- amitchandak
mmoroni , A new column like
maxx(filter(table, [channel] =earlier([channel]) && [ch#] <> 100),[ch#])
or
if([ch#] =1000,maxx(filter(table, [channel] =earlier([channel]) && [ch#] <> 100),[ch#]), blank())
- mmoroni
Thank you so much!!!! I used the IF statment, so i could leave the NON 1000's blank.