Forum Discussion

Walt1010's avatar
Walt1010
Helper V
2 years ago
Solved

Noob Modelling Question re Select Equivalents

I have 2 tables of phone calls, one incoming calls and 1 outgoing. Both are joined to a phone directory table that contains their numbers. The joins are both many-to-one. Both tables are also linked to a date table.

I would like to display all numbers that have incoming and outgoing calls, together with other info from the directory table. Ideally I would like to display this information by month.

I've tried just adding the phone number columns from incoming and outgoing to a table, but that didnt seem to have the effect of  displaying ones that have the same number. Any ideas would be welcomed!

 

 

 

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Walt1010 

     

    Thank you very much amitchandak for your prompt reply.

     

    I found your question when I checked the forum, may I ask if your problem has been solved? If not, here are some ideas.

     

    Here's some dummy data

     

    “Incoming Calls”

     

    “Outgoing Calls”

     

    “Phone Directory”

     

     

    Create a new table that combines both incoming and outgoing calls.

     

    CombinedCalls = 
UNION(
    SELECTCOLUMNS(
        'Incoming Calls',
        "PhoneNumber", 'Incoming Calls'[PhoneNumber],
        "CallType", "Incoming",
        "Duration", 'Incoming Calls'[Duration],
        "Date", 'Incoming Calls'[Date]
    ),
    SELECTCOLUMNS(
        'Outgoing Calls',
        "PhoneNumber", 'Outgoing Calls'[PhoneNumber],
        "CallType", "Outgoing",
        "Duration", 'Outgoing Calls'[Duration],
        "Date", 'Outgoing Calls'[Date]
    )
)

     

    Create a column to identify phone numbers that have both incoming and outgoing calls.

     

    HasBothCalls = 
CALCULATE(
    COUNTROWS(CombinedCalls),
    FILTER(
        VALUES(CombinedCalls[PhoneNumber]),
        COUNTROWS(
            FILTER(CombinedCalls, CombinedCalls[PhoneNumber] = EARLIER(CombinedCalls[PhoneNumber]) && CombinedCalls[CallType] = "Incoming")
        ) > 0 &&
        COUNTROWS(
            FILTER(CombinedCalls, CombinedCalls[PhoneNumber] = EARLIER(CombinedCalls[PhoneNumber]) && CombinedCalls[CallType] = "Outgoing")
        ) > 0
    )
)

     

     

    Create a relationship between the Phone Directory table and the CombinedCalls table.

     

     

    Filters the record for which HasBothCalls is 1.

     

     

    And you can add a month slicer for filtering. Here is the result.

     

     

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Walt1010 , The phone number should come from the phone directory table, the common table. When you want to make a measure work across two fact tables, they need to be displayed against common dimensions.

    • Walt1010's avatar
      Walt1010
      Helper V
      2 years ago

      Hi there, so if I've got those relationships in place between the 3 tables, and I wanted to produce a visualisation showing common numbers between incoming and outgoing tables, I can create a table chart with the phone number for the directory table and other details from the other 2 details, that should do it?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Walt1010 

     

    Thank you very much amitchandak for your prompt reply.

     

    I found your question when I checked the forum, may I ask if your problem has been solved? If not, here are some ideas.

     

    Here's some dummy data

     

    “Incoming Calls”

     

    “Outgoing Calls”

     

    “Phone Directory”

     

     

    Create a new table that combines both incoming and outgoing calls.

     

    CombinedCalls = 
UNION(
    SELECTCOLUMNS(
        'Incoming Calls',
        "PhoneNumber", 'Incoming Calls'[PhoneNumber],
        "CallType", "Incoming",
        "Duration", 'Incoming Calls'[Duration],
        "Date", 'Incoming Calls'[Date]
    ),
    SELECTCOLUMNS(
        'Outgoing Calls',
        "PhoneNumber", 'Outgoing Calls'[PhoneNumber],
        "CallType", "Outgoing",
        "Duration", 'Outgoing Calls'[Duration],
        "Date", 'Outgoing Calls'[Date]
    )
)

     

    Create a column to identify phone numbers that have both incoming and outgoing calls.

     

    HasBothCalls = 
CALCULATE(
    COUNTROWS(CombinedCalls),
    FILTER(
        VALUES(CombinedCalls[PhoneNumber]),
        COUNTROWS(
            FILTER(CombinedCalls, CombinedCalls[PhoneNumber] = EARLIER(CombinedCalls[PhoneNumber]) && CombinedCalls[CallType] = "Incoming")
        ) > 0 &&
        COUNTROWS(
            FILTER(CombinedCalls, CombinedCalls[PhoneNumber] = EARLIER(CombinedCalls[PhoneNumber]) && CombinedCalls[CallType] = "Outgoing")
        ) > 0
    )
)

     

     

    Create a relationship between the Phone Directory table and the CombinedCalls table.

     

     

    Filters the record for which HasBothCalls is 1.

     

     

    And you can add a month slicer for filtering. Here is the result.

     

     

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.