I have 2 fact tables which I would like to filter simultaneously, based on a common column (in this case a Company name)
Let's say Fact Table 1 contains market data (e.g. how many jobs have been performed by ALL suppliers for a given company), whereas fact table 2 contains my own data (e.g. how many jobs have I performed for a given company), and I want to track how my activity has been compared to the total market.
One issue is that users have entered data manually (as opposed to using drop down menus), meaning the data contains typos.
One company called "ABC 123" can be entered as "ABC 123", "ABC123", "ABC-123" or even "ABC 1234" etc.
Market Data.PNGMarket Data.PNG
My data.PNG
I added all possible company names in one table and added the "correct" company name next to it.
Based on this table, I have created a query where I removed the first column and removed duplicates, so I have a list of correct and unique companies.
Unique data.PNG
Then I created the following relationships:
Relationships.PNG
I then created a pivot table off Facts Table 1, and added the "Company Name New" from the "Unique Companies" query to the filter.
When I then selected a company in the filter, the data does not get filtered.
Anyone knows what I am doing wrong?
Bookmarks