Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Direction on managing multiple datasets

Hello all,   I am pretty new to PBI. I have been working with it long enough to make some decent reports but I am running into an issue now. I am trying to figure out the best way to manage and sha...
Need Help
healthEteam's avatar
healthEteam
Resolver I
7 years ago

Are the excel spreadsheets that each one is completing the same?

 

If so you could possibly use the append feature in the Power Query Editor to combine each of the datasets into one.  You would want a column either added to the excel spreadsheets that designates which facility the data was coming from or add a custom column on each of those datasets in power query prior to the append.

 

Then you can have one report.

 

Grant the users access to the report and then in security you could limit each of them to their respective facility using row level security.

 

I havent setup the online scheduled refresh from the one drive folder but I think that is pretty common.

 

The new dataflows could possibly play a role here as well if you wanted reuse that dataset with multiple reports.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks for the response healthEteam. I'll check out the append feature. I would worry about the size of the report though if I combined each dataset into one. Right now with just one report with one dataset it takes roughly 1-2 mins to refresh all the data when manually refreshing. I'm still having trouble with the scheduled refresh so I have not done that yet but I imagine it would be similar. Each dataset source I'm pulling from is roughly 1-2MB with multiple excel tables from multiple workbooks. The .PBIX report file itself, again with just one of those datasets, is about 1MB too for reference. If I combined all 10 locations I worry that size might go up to 10-15MB.