Find matching values within multiple columns from different table

[PNDC_MLR]

Hello everyone!

Our customer sends us a list of businesses that we provide services for. Each business has a unique CustomerID number. I'm trying to get the data from a second table where we store all the ticket data. Unfortunately the unique CustomerID could be amongst 3 different columns within the ticket data table as the CustomerID is sometimes assigned by one of our tools, so the columns in the ticket data table are 

1. Ticket_CustomerID

2. Tool1_CustomerID

3. Tool2_CustomerID

However, most times Ticket_CustomerID and Tool1_CustomerID are the exact same, sometimes they aren't. Sometimes neither Ticket_CustomerID nor Tool1_CustomerID match with the list of businesses CustomerID but there's a match with the Tool2_CustomerID. 

What I'm trying to do is to get all ticket data (all rows from the ticket table) where the CustomerID from the list of businesses is matching with either one of the 3 ticket data CustomerID columns, e.g. find matches between CustomerID and Ticket_CustomerID, if not matched, look for matches between CustomerID and Tool1_CustomerID etc. but I would also need to make sure that there are no duplicates so I would need to avoid to collect the ticket data twice if Ticket_CustomerID and Tool1_CustomerID are the same value. 

Tables

List of Businesses

CustomerID	Business Name	Assigned to Employee
1234	BusinessNameD	EmployeeA
2234	BusinessNameX	EmployeeB
3234	BusinessNameY	EmployeeC

Ticket Data

TicketNumber	Assigned to	Ticket_CustomerID	Tool1_CustomerID	Tool2_CustomerID
1	EmployeeA	1234	7785	null
2	EmployeeB	2234	2234	5478
3	EmployeeC	6345	null	3234

There is of course more data in the ticket table, multiple tickets per business, multiple tickets per employee, contact dates, contact channels, accepted services etc. but no relevant data that could help identify the tickets that are matching the CustomerID.

Any help would be much appreciated!

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the expected result very clearly.

[lbendlin]

Use COALESCE.

[hnguy71 · Accepted solution]

Hi @PNDC_MLR ,
You're looking at a basic data model where you have one dimension and one fact table.

You should be able to achieve this by unpivoting your ticket and customer columns from your ticket data table like so:

Once loaded, create a relationship between your fact and dimension by the customerId column:

Now, all that's left is to take your customer id from your list of businesses and place it in a visual with an aggregate measure, such as count number of tickets per customer.

Here's an example:

CountOf.Tickets = DISTINCTCOUNT(TicketData[TicketNumber])

And sample output of the measure:

If this isn't the result you're looking for, please do provide a sample output for additional clarity. 

[PNDC_MLR]

Hi @hnguy71 thank you very much for the suggestion. 

I was actually able to make it work with some additional steps. Why unpivoting was also something I've thought of, I wasn't able how to handle the duplicate ticket data. What I've shown here is a very simple example of what we're actually working with, there are more dimensional tables for time and business filtering and already established measures that have shown incorrect numbers with the duplicate ticket data as we were also looking at different KPIs on employee level. 

In order to remove the duplicates I've joined the list of businesses on the new customerID column and was then able to remove duplicate ticketnumber rows while still maintining the list of tickets mapped to the customerIDs. 

Thank you so much!