Noob Modelling Question re Select Equivalents

[Walt1010]

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!

[Anonymous · Accepted solution]

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.

[amitchandak]

@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]

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?