Forum Discussion
Multiple client names for the same client.
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!
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).
4 Replies
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.
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.
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).
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]