Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filter based on data between 2 dates and other matching criteria from another table

Hi

 

I have 2 tables which aren't related. The first holds call data, time of call, where it was routed to etc. The second holds audio information which was manually added to the line at certain points of the day.

 

I am looking to see how many calls came through when certain audio was placed on the line. 

 

I want to add a column in my first table (call data) which shows the audio file name if there was a messge on for that type of call and that type of message was used for a certain purpose at the time of the call. 

 

My Call Data table includes:

Call datetime (date time)

type of call (text)

 

my Audio table includes:

start datetime (date time)

end datetime (date time)

type of call (text)

audio filename (text)

used for (1 or 0)

 

When a call is stored in the first table it has the date time it was made and the type of call,

 

so I need to populate the audio filename into the call data table if:

the call type matches

the call happened between the start and end datetime

there is a "1" in the used for column

 

any help please?

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Create a calculated table first.

    temptable =
FILTER (
    CROSSJOIN (
        FILTER ( 'Audio table', 'Audio table'[used for ] = 1 ),
        SELECTCOLUMNS (
            'Call Data table',
            "Call Datetime", 'Call Data table'[Call datetime],
            "Type", 'Call Data table'[Type]
        )
    ),
    [Call datetime] >= [start datetime]
        && [Call datetime] <= [end datetime]
        && [type of call] = [Type]
)

     

    In Call Data table, add a calculated column.

    FileName =
LOOKUPVALUE (
    temptable[audio filename],
    temptable[Type], 'Call Data table'[Type],
    temptable[Call Datetime], 'Call Data table'[Call datetime]
)

     

    Best regards,

    Yuliana Gu

3 Replies