Forum Discussion
Common filter : 3 Tables
Hi All,
I have 3 separate tables as shown below with different columns.
Now I want to have a common filter on the dashboard for "Name" where there are no duplicates but operate filtering across all the 3 tables.
How can I achieve this?
Thanks
I don't know why you have 3 geography table with sales in only one. In such case, Open power query. Create a new query by appending all three tables, since all have common columns
Name, City, Country.
See Image below:
Now removed column PIN and Sales. By using city and country you would be able to use geophy visuals. But if you need PIN, You can keep. But we will remove duplicates where name, city, and country matches. After selecting all three columns, click on remove duplicates. See image below:
After that need a key to match. Creat a custom key by merging all three column with "|" pipe separator. See image below:
You are done with geography table. Now create sales fact table. Again I don't know, why you have sales only from few region but not for all region. Duplicate table3, rename to sales. Create the same geokey by applying above method. Then delete all 3 name, city and country column. Now you sales table with only sales and geography key. See image below:
Now disable loading all the 3 tables and only load geography and sales table in power bi engine to create relationship. See image below:
Now you can do your all the calculation based on this setup.
Hope this helps!!If this solved your problem, please accept it as a solution!!
6 Replies
Hi Anonymous ,
Create a new table with the Names and then create relationships one to may with all other 3 tables that will allow for you to have the filtering needed.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships
Hi Anonymous You can create a table of unique name using dax or power query. Using Dax (See image below):
Then create relationship using this column and use this column as a slicer. See relationship image:
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
If there is more than one active relationship between 2 tables you may face such errors. If, make one of the relationship inactive, and use USERELATIONSHIP function to develop measure. Also, cross filter direction both and inter relationship among the table can cause this error. It is better to identify dimension and fact tables to develop star schema. You can create geography dimension table and sales fact tables. Then create one to many relationships from geography to sales.z
Hope this helps!!
