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

[Anonymous]

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?

[v-yulgu-msft · Accepted solution]

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

[Anonymous]

@v-yulgu-msft you my friend are an absolute genuis and I am forever greatful for the help.

Worked like a dream, after I amended it slightly with some other filters etc.

Keep up the great work and I might be back if I get stuck again.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365