Forum Discussion
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 po...
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
v-yulgu-msft
Microsoft Employee
Microsoft EmployeeHi 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
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.