Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My data comes from different sources and in each source the names for my clients are different. So in my fact table name column I could have "Mr John Doe", "Mr J Doe" and "John Doe" but they all represent the same person.
Currently, I have a ClientNameMap table where ClientNameAlias is the primary key which looks as follows:
Key | ClientName | ClientNameAlias |
1 | John Doe | Mr John Doe |
2 | John Doe | Mr J Doe |
3 | John Doe | John Doe |
Then I have an additional lookup table for ClientInfo about the client.
Key | ClientName | City | Company |
1 | John Doe | London | Big Company |
2 | Jane Smith | Miami | Small Company |
Should I relate ClientInfo[ClientName] to ClientNameMap[ClientName] ?
Or should I relate ClientInfo[ClientName] to ClientNameMap[ClientNameAlias]?
In both cases I then relate ClientNameMap[ClientNameAlias] to Sales[ClientNameAlias]
Then I merge Sales with ClientNameMap on the ClientNameAlias column and expand ClientName so that if I want to display a table I can display the official ClientName and not all the different aliases.
Does this make sense? How should I be dealing with the different names for a single client?
Solved! Go to Solution.
I think, judging from what you've shown us. You need to create a relationship between the 2 'Clientname'
The reason you can create a relationship with clientnamealias aswell, is because (in ur example) 1 in 3 have a relation.
With the way you data is set-up though, you might in some scenarios see 'John Doe' three times, so be careful about that.
I think, judging from what you've shown us. You need to create a relationship between the 2 'Clientname'
The reason you can create a relationship with clientnamealias aswell, is because (in ur example) 1 in 3 have a relation.
With the way you data is set-up though, you might in some scenarios see 'John Doe' three times, so be careful about that.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |