Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Two relationships, one table, one slicer help!

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 n...
Need Help
nirvana_moksh's avatar
nirvana_moksh
Icon for Impactful Individual rankImpactful Individual
7 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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 IDCaller NumberCalling UserCalled NumberCalled UserTIME OF ORIGINATIONTIME OF DISCONNECTION
      12000LJAMES2001LNOON15/04/2018 12:3215/04/2018 12:36
      22001LNOON5000HPEPPER15/04/2018 12:3615/04/2018 12:39
      32003KJB2000LJAMES15/04/2018 12:1215/04/2018 12:20
      42004UKNOWN5333JCMON15/04/2018 12:3215/04/2018 12:36
      52001LNOON4555BOATYMC15/04/2018 12:0215/04/2018 12:06
      62003KJB2001LNOON15/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.