Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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 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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |