SharePoint online multiple lists power BI

[Anonymous]

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

1. It’s a basically of the HR system where HR managers for specific regions (Indonesia, Thailand, Singapore, Japan) 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 Nintex 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. Each regional HR, for e.g. Indonesia, will fill on all employee details and other details in the Form.
   
   They also should be viewing his/her own dashboard for that region. I will be using Power BI specific regions.
   
   The requirement is that each region SHOULD NOT ABLE to access other regions for confidential purposes.
   
   
3. Global HR should be able to view all the regions.
   
   
   For e.g.
   
   All the data would be pulled on all the regions into one form.
   
   

I will assign a separate SharePoint groups for the Global HR.

My Approach and clarification on Power BI  

As there will be data into single form, I am planning to split into each list for e.g. Employee List, Salary List, Productivity List and Exchange rate so that there are no performance issues like Form slowed load when HR admins adding more data into this form. 

Q: Is it possible to combine all these lists in a regional HR dashboard (specific regions) and Global dashboard (all regions)?

I referred to this blog post https://www.abelsolutions.com/creating-power-bi-reports-sharepoint-lists/  

Any related articles or blog references would be highly appreciated.

Thanks in advance.   

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

If data structure of all the regions such as (Indonesia, Thailand, Singapore, Japan) are same, you can combine them into one. 

Best Regards,
Angelia

[v-huizhn-msft]

Hi @Anonymous,

Q: Is it possible to combine all these lists in a regional HR dashboard (specific regions) and Global dashboard (all regions)?

You means combine all the lists into one dataset? If it is, you can select all lists when you import data like the following screenshot.



Then you can combine all the table in Query Editor, you can review: Append vs. Merge in Power BI and Power Query. And there is a similar thread for your reference.

In addition, when you import all data, you want to restrict different HR's access to view data, you can use Row-level Security in Power BI. And I post some articles, you can review them.

Using Username() in DAX with Row-Level Security 
Row Level Security in Power BI
Dynamic Row Level Security with Power BI Made Simple

Please feel free to ask if you have any other issues.

Best Regards,
Angelia

[Anonymous]

@v-huizhn-msft and Angelia ..

many many thanks for this!!! 

So I am able to company using links above ...

final question 

Q: I will be using single data entry form for all the regions such as (Indonesia, Thailand, Singapore, Japan) and I will generate all the form data for all the regions from the that single data entry for Global HR. 

Using links above can I achcive this? 

Thank you!! 

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

If data structure of all the regions such as (Indonesia, Thailand, Singapore, Japan) are same, you can combine them into one. 

Best Regards,
Angelia

[Anonymous · Accepted solution]

@v-huizhn-msft thank you!