Merging Queries using SharePoint lists

  Hi All,

I am working on a project where majority work in based on Power BI reports. These are the customer requirements.

1. I t’s a basically of the HR system where HR managers for specific regions (Indonesia, Thailand, Singapore, Japan etc.) would be filling in the employee information such as number of employees, average salary for 6 months, number of training completed, number of years in services, Age profile for each region, etc.

   HR Admins will be filling these numbers within SharePoint  form.

   Company split the financial periods into two: 1st FY2017 and 2nd FY2017.

   This is sample proof of concept form that prepared for the customer:
   
   
   
   
   
   

2.  I have designed the following Lists so that I could model them using Power BI and create reports for each region and other parameters that is described above  
   
   
3.  List 1: Region (aka Country)
   
   Title column field and I renamed title field to Region as follows: 
   
   
   
   
4. List 2: Subsidiary
   
   Regions could have one or more subsidiary and I again lookup that refer to region as follows:
   
   
   
   
   
   
5. List 3: Exchange Rate
   
   
   Each region could they own currency and I again used Region lookup for exchange rate as follows
   
   
   
   
6. List 4:  HR Dashboard.
   
   It’s the main list that stored all the information such as in the employee information such as number of employees, average salary for 6 months, number of training completed, number of years in services, Age profile for each region, etc. and it lookup to other lists for regions, subsidiaries and exchange rate and it’s for the scalability of the application and admins can add more regions, subsidiaries and exchange rate at the later stage.
   
   These 3 rows are sample data. 
   
   
   
   
   IN POWER BI DESKTOP 
   
   
7. I need to show a report for all these lists, Exchange Rate, HR Dashboard, Region and Subsidiary as follows: 
   
   
   
   
8. The relationships are using ID columns:
   
   
   
   
9. From the HR Dashboard list, I dragged Period and RegionID (Lookup to Region list) I dragged Region column and then I dragged  Subsidiary column
   
   and it resulted in this:
   
   
   
   

when I merge the lists using inner join, it joins using ID which I can’t use it for my purpose.  




How could I join those 4 lists so that I could use Bar chart and stacked column chart for my reports?   

Any help would be highly appreciated.