Merging Queries using SharePoint lists

[Anonymous]

  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.   

[v-jiascu-msft]

Hi @Anonymous,

The relationships could be wrong. Though they are all called "ID", they have different meanings. If we establish proper relationships, we don't need to merge the tables. We can create some measures and plot them in the visuals. Could you please post a sample in TEXT mode? The dummy PBIX file would be great. I can't give any more suggestions without a sample.

Best Regards!

Dale

[Anonymous]

@v-jiascu-msft and Dale ...

Sure ...I will supply the sample shortly what the client is looking for 

[Anonymous]

@v-jiascu-msft 

Client is looking for regional dashboards (Indonesia, Thailand, Singapore, Japan etc.) and GLOBAL Dashboard (All Regional) 

These are samples of GLOBAL Dashboards 















These are sames of Regional Dashboards 

Please Advice how could use my design to gerenarte these charts and dashboards? 

[Anonymous]

Regional Dashboards such as Indonesia will have its own Indonesia subsidiaries which I have indicated as BU1, BU2, BU3 etc.  

Each Region SHOULD NOT be able to view other regions such as Japan, Thailand etc. for the confidentiality purposes but the GLOBAL HR should be able to view all the regions.

[v-jiascu-msft]

Hi @Anonymous,

To be honest, I can't do much without source data. Only some suggestions.

1. Creating visuals could be easy. Drag columns into fields. 

2. Calculated columns and measures could be needed.

3. How do you want them to use the Dashboards? Create dashboards themselves? Or share to them and view only?

4. You can apply RLS (Row-level-security) to separate the regions. 

If you can provide the dummy PBIX file (your file with four tables), that would be great. 

Best Regards!

Dale

[Anonymous]

@v-jiascu-msft and Dale. 

Totally got you ...

Will provide the PBIX file with sample data shortly ...

Thanks a ton!

[Anonymous]

@v-jiascu-msft

Please find attached Sample - HR.pbix file  

 I will be using Power BI free edition and not the Power BI Pro edition.


Q: Is RLS available on the Power BI free edition?  

Design data where users would be storing.




This is the main data form where users would be filling.  




This is the main HR dashboard data structure multiple fields and three lookups fields to region, subsidiary and exchange rate as follows: 




Each region would be its own subsidiaries as follows:


and this is the subsidiaries data structure



Exchange Rate Data:

Exchange Rate Structure:

Power BI Desktop 

These are the all the fields from those 4 SharePoint lists:






In the relationship tab, HR dashboard has no relation with other lists.

I made one-to-one relation for HR Dashboard and Region. and mapped RegionID of the HR Dashboard list to the Region list. 






So I looking for charts and dashboards that I showed above.


Thanks for your help.

[v-jiascu-msft]

Hi @Anonymous,

Please check out the file: https://1drv.ms/u/s!ArTqPk2pu-BkgR8FU0DpC82dp9Y9.

What I have done:

1. Rename 'exchange rate'[id] to 'exchange rate'[exchangeRateID];
2. Rename 'subsidiary'[id] to 'subsidiary'[SubsidiaryId];
3. Rename 'region'[id] to 'region'[RegionID];
4. Change relationships;

5. Create two visual.

Questions:

1. No "bu1", "bu2", "bu3" in the model. So I can't create a visual with them.

2. How to define these terms? There are PCE and FTPE. For example: Turnover.

Your question: Is RLS available on the Power BI free edition?

A: Yes, RLS is available for all accounts. But sharing is only available with Pro licence.

Best Regards!

Dale

[Anonymous]

Thanks a ton @v-jiascu-msft and Dale 

Questions:

1. No "bu1", "bu2", "bu3" in the model. So I can't create a visual with them.

The requirements are each Region such as India will have its own Subsidiary which in my example are Mumbai and Delhi.



2. How to define these terms? There are PCE and FTPE. For example: Turnover.

Company defined Employee Turnover as follows

No of Full-Time Employees (FTSE ) + No of Permanents Contract Employees (PCE) for 1st Half 2017= 99

No of Full-Time Employees (FTSE ) + No of Permanents Contract Employees (PCE) for 2nd Half 2017= 108

In 1st Half 2017, No of Hirers = 0
In 2nd Half 2017, No of Hirers = 0

In 1st Half 2017, No of Hirers = 2
In 2nd Half 2017, no of leavers = 12,

Employee Turnover is computed as for 1st Half 2017 = [(0 /((99 + 99)/2)]*100 = 0
Employee Turnover is computed as for 2nd Half 2017 = [(2 /((99 + 108)/2)]*100 =0.0193 ~ 0.02
I guess, DAX can be used for this.

Q: Could you please show how you change the relationships?
 Is it possible for samples Visuals for BUs and Employee Turnover?

Thanks!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

1. You can add Region to the Legend of the visual.

2. Do you have any source data? You only have summarized data in the HR Dashboard. How to update the totals? The formulas of turnover have to be hardcoded.

3. You can delete the old relationships and rebuild new ones. Or double click the relationship to make changes. 

4. To be honest, it's easy to create a visual. Drag the columns to the fields. 

Best Regards!

Dale

[Anonymous]

Thanks @v-jiascu-msft and Dale for all your help ...

I am able to generate reports via Power BI ..

Thanks again!