Multiple client names for the same client.

[Frankmeister1]

Hi all,

I'm currently building a report where I want to show Sales per client, hours spend per client and more. But I have an issue with my data. The data that I input through Excel has mulitple names for one and the same client.  Example

Client name:	Client name 1 in Sales system	Client name 2 in sales system	Client name 3 in sales system
John Doe	John Doe Europe	John Doe USA	John Doe Asia
PowerBI	PowerBI Europe	PowerBI USA
Peter Pan	Peter Pan Europe

The "client name" is the one that is used in our clock-in system to register hours. 

In our sales system we have different names for different regions, but I want to showcase them in a report as 1 entity. I.E. When I select John Doe on a slicer I want it to show all the sales from the three different names combined. 

Currently whenever I select John Doe, my report only shows the data for John Doe Europe because this is the relation I build. 

When i try to connect Client name 2 and/ or client name 3 to the sales data table it tells me it's a many to many relationship and it doesn't work. 

Is there anybody that could please help me solving this problem!

Thanks!

[williamadams12]

A fairly reliable, but tedious and inexact solution would be to incorporate a transformation table into your ELT process, and create the relationships for the revised client naming conventions that way. 

However, I find handling some of the data transformations using SMSS in a SQL script to be more flexible and reliable than just uploading an excel workbook and then managing them in PowerQuery. E.g., you can create multiple aliases and then assign one, standardized, specific name to multiple naming conventions used for the same client to help standardize them more efficiently and easily. This approach isn't perfect, but it's simpler, easier and faster if you can configure the dataset in a SQL table. 

E.g., 

SELECT 

CASE WHEN [Client Name] LIKE '%Doe%' THEN 'John Doe'

WHEN [Client Name] LIKE '%Power%' THEN 'Power BI'

ELSE [Client Name] END AS [Client Name]

FROM [Data Source]

[AntonioM]

I would create a table only with two columns, one for the Client Name and the other for the different ways it appears.

Client Name	Client Name in Data
John Doe	John Doe Europe
John Doe	John Doe USA
John Doe	John Doe Asia
PowerBI	PowerBI Europe
PowerBI	PowerBI USA

Create a relationship between Client Name in Data and the name column in your data, then you can use Client Name in any slicers or tables.

[Frankmeister1]

Hi, 

This solution doesn't work 100%. Because I connect my sales data through a relationship with Client name in Data. Then I can use the slicer like you said and it works. But the clock in data I have uses the "client name" names. Which means that if I connect my clock in data and "client name" it will give me a many to many relationship because there is mulitple John Doe in Client name.

[AntonioM · Accepted solution]

Ok, It would be possible to use the many to many relationship. You can set the filter direction to be single and then use the column 'Client Name' and it will filter like normal. You'll need to check no calculations have changed (such as a SUMX over the client table would be 3x as many now) but overall you can get the same result.

Another way would be to create one more table, just with the list of client names, each once. Then you can link this table to both the table in my other response and your clock in table and use that for the slicer.

Now when you select John Doe from this new table:

• John Doe is filtered from the clock in table
• John Doe is filtered in the name variations table (Client Name)
• The name variations table filters sales by all of the different John Doe variants (Client Name in Data).