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