Forum Discussion

mmoroni's avatar
mmoroni
Icon for Helper I rankHelper I
5 years ago
Solved

index/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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
mmoroni
Icon for Helper I rankHelper I
5 years ago

Ch# 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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
    mmoroni
    Icon for Helper I rankHelper I
    5 years ago

    Thank you so much!!!!  I used the IF statment, so i could leave the NON 1000's blank.