I will try to explain my issues as clearly and as concise as possible.
I am building a dashboard for my departement, made of 6 different reports with data coming from different sources (csv files, SQL servers, Salesforce). The two dimension that link all data and reports are a dim_date, and what I am trying to build right now, which is a location/country dimension. My problem is that the various dataset that I use for the reports are based on slightly different location/country dimensions. In total I have 3 different location dimension, which I could say are also hierarchically dependant.
At base level I have the country name, then on the next level I have the Country Organisation name, which includes country names and some group of countries such as DACH and BeNeLux and then I have Business regions names which, as the name implies, might include groups of countries and/or Country Organisation.
I can get three separate tables of distinct names for the three dimensions (country, country organisation and business region) with power query, but how do I put them in relation so that I can then use them to filter the reports as needed?
I am fairly new at PowerBI, but as far as I understand, afetr creating the 3 tables with the distinct elements for each dimension, I could in theory create a fourth table with 3 columns: the first one would be again a list o distinct elements of the "first level" (simple country names), the second column would be populated by Country Organisation names (some repeating more than once, e.g. for Belgium and the Netherlands) and then a third colums again populated by Business Region names.
Does this make sense? If so, would it be bettere to create the fourth table manually outside of PowerBi (excel file) and just load it into the model or should I do it from inside PowerBi?
If this is not the way it is suppose to be done what am I missing/doing wrong?
Thank you very much for your help.
Best,
Seba
