I am struggling with something so basic…but so important to my reporting. In my Orders table and my Shipped table, we have a column for Country and Region.
I am simply trying to create a dimension table(s) for Country and Region. Then relate those to my Orders table and Shipping table so that I can apply a “global” filter to on my report that will filter Country and/or Region at the page level.
Been working in the query editor to try and figure this out but keep getting many to many errors etc. Any advice how to set this up would be much appreciated!
You can follow the steps given below in order to avoid “Many-to-Many” relationships that you’re facing.
Step 1: Create a “Duplicate table” from your Order Table.
Step 2: From the duplicate generated table remove all other columns and only keep “Regions” and “Country” column.
Step 3: Right - click on the Regions column and select the “Remove Duplicates” options. And now you’ll be only left with the unique value list of Regions and Country.
Step 4: From your Order Table as well as Shipping table remove the “Country” column from both the tables. This step is “Optional”. I’ve just recommended this step just to reduce your data size from both these tables.
Step 5: Click on the “Close & Apply” button.
Step 6: Under the Modelling Tab view, now try your Order Table and Shipping Table to get connected with the newly created Locations table.
Please Note: In your Order Table and Shipping Table you should’ve same number of “Regions” and “Countries”. If there’s any error in designing your data for these table then get it sorted first.
Hoping you find this useful and helps you to avoid “Many-to-Many” relationship.
Hi Harsh - Thank you. This might be workable, however, we have another table (our CRM table) that definitely will include countries that have not shown up on the Orders table. For example, opportunities in our pipeline in Sweden that have not turned into Orders. So if I use the Orders table as my base table, it will miss Sweden.
Ideally I’d like a solution that allowed me to use a Country/Region table that I already have that list all of the regions and countries of the world.
So you can do one thing. You can apply this same steps on the Shipping Table as well and then Append this two tables and convert them into one. By this way you’ll have “Regions” and “Countries” from both these tables.
Since you’ll still have Regions in all of your table and relationship will be created based on the “Regions” column so then you’ll still be able to analyze your Countries. And as I said this step is simply optional.
As you can imagine, a table with Regions and Countries is going to have many duplicates in the Region column. And of course the Orders table and Shipments table will also. This was my original problem really…which is how to construct the tables and data model based on this scenario. The Regions field can of course be unique…but as soon as you add Countries to that table there are many countries part of one region, so you are back to a many to many problem when trying to relate to the Orders table or Shipments table.
@richmont when I run across this type of issue in my functions, I create a hybrid column.
Example, in my primary data set, I have “Customer Number” and “Ship-To Number” - each customer can have multiple Ship-Tos in my data, and the Ship-To numbers can be duplicated (in fact in many of my customers, they have Ship-Tos 001, 002, & 003).
And the invoice data is related to both the Customer and the Ship-To (and yes, I have a need to report to the Ship-To level).
I have taken the Customer Number and Ship-To number and created a new column (in the Query Editor) that combines the two with a unique identifier (in my case I use double colons :: ). This is done on both the Invoice and the Ship-To tables, and the column is only used to create a unique key to join the tables.
I’m kinda wondering if this might be easier. I did a google search " Region and countries in the world"
this is the link that have all 197 countries with Region.
You could load into excel and query into power bi or maybe there is a way to do it directly from the web. This way you have all countries in world in one table.
