Forum Discussion
Two relationships, one table, one slicer help!
Caller id
Called id
Call id(unique)
Time of origination
Time of disconnection
I want to be able to use one slicer filter to select a name that shows the count of calls, by date in a visualisation that shows a count if calls they made AND the calls they received in two lines over the same date based axis.
Tables:
Namekey
Calldata
Namekey/manager active directory
Is there any way to do.this?
3 Replies
nirvana_mokshImpactful Individual
I believe there is, but you might have to post some sample data and a little more detail.
- Greg_Deckler
Community Champion
I agree with nirvana_moksh. 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/409365My thought is that you are going to want a separate Date table. Use that as your axis. You can then write measures that get the MIN and MAX dates and then FILTER your table for rows between those dates. You would have two of these, one for line you want to show. But, again, really difficult to conceptualize what you are going for without sample data and example of desired output.
Hi Greg, your formula has been really useful below and using the generate table helped me out with another dashboard i had, so really great thanks!
nirvana_moksh thanks, here is some sample data:
This is a sample of call data:
I want to be able to ue a slider that once i set it to the PERSON ID
Call ID Caller Number Calling User Called Number Called User TIME OF ORIGINATION TIME OF DISCONNECTION 1 2000 LJAMES 2001 LNOON 15/04/2018 12:32 15/04/2018 12:36 2 2001 LNOON 5000 HPEPPER 15/04/2018 12:36 15/04/2018 12:39 3 2003 KJB 2000 LJAMES 15/04/2018 12:12 15/04/2018 12:20 4 2004 UKNOWN 5333 JCMON 15/04/2018 12:32 15/04/2018 12:36 5 2001 LNOON 4555 BOATYMC 15/04/2018 12:02 15/04/2018 12:06 6 2003 KJB 2001 LNOON 15/04/2018 11:12 15/04/2018 11:16
then i use active directory lookup to convert both the Caller ID and Called ID into their full names via a separate table:
So LNOON becomes Luke Noon
i want to have a filter/slicer on the page so that i can select the Full Name Luke Noon and on a single visulisation show a line graph that shows his outgoing and incoming calls
so in this instance,
Luke Noon makes 2 calls and also recieves 2 calls at these times on that date.
to calculate the calls, i would do a distinct count of call ID or a count row.
i am having problems, because i need 2x relationships, one from Calling User and one from Called User to the active directory
i created a separate table:
Aggregate user = DISTINCT(UNION(SELECTCOLUMNS(data, "Calling", data[CallingUser]), SELECTCOLUMNS(data, "Caller", data[CalledUser])))
I have a custom column that looks up from the active directory:
Person ID= LOOKUPVALUE(user[Co-Worker],'Aggregate user'[Calling], 'Aggregate user'[Calling])
this give sme the Full name field of the user.
edit Greg_Deckler
Can the Tickets Open measure work per Minute? ie can we show how many calls are open on any given minute, and not just per day as obviously calls are concurrent by the hour.minute not by the day,
thanks again for your help.
