Two relationships, one table, one slicer help!

[Anonymous]

So I gave some call stats including the following fields:
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?

[nirvana_moksh]

I believe there is, but you might have to post some sample data and a little more detail.

[Greg_Deckler]

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/409365

My 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.

[Anonymous]

@Greg_Deckler

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.