Personalising a Power BI view or table to the person viewing it

[Brister]

Hi All. Thanks for any help.

I am trying to create a Power View that 'knows' who the currently logged in user is to tailor the information displayed e.g. a timesheet dashboard that will show a Manager a PowerView showing them 'My Staff' rather than all staff. I'm using SharePoint 2013 (on premises) and Excel 2013.

In 'normal' excel, I am using VBA and a UDF to determine who the current user is, and passing a parameter to a SQL stored proc to return just the relevant data set. I can give them a 'normal' PivotTable with slicer's etc. but this has none of the power and flexibility of PowerPivot/PowerView. The latter isn't going to support VBA Projects.

I understand (I think) that it is not possible to pass a parameter to a query or stored proc when the user opens a PowerPivot workbook or PowerView in the browser. The idea of PowerPivot etc. is that the data is already loaded from SQL and other sources and refreshing the dataset is (or can be) scheduled centrally - it isn't fired off every time a user opens the report.

Tailoring a report to show the information that is relevant to the Manager gives them immediacy & relevance, plus it helps me lock information down to just the silos / business units that person is responsible for.

Do I end up needing to create bespoke reports for each user to give them the convenience of seeing just the data that is relevant to them? (not very convenient for me!) Or I provide them with all the data and require them to use filters, slicers etc - more effort for them, but I may not want to share information across the board like that.

Is there some aspect of SharePoint/PowerPivot/PowerView/Web Parts that would allow me apply business logic based on who is viewing the report to display only the information that is relevant to them?

Many thanks.

 

[scottsen]

There are few options here.

I haven't done this much, but you could probably write your measurs just write and use the USERNAME() function in DAX: USERNAME Function (DAX)

Since you are using SharePoint, what I typically do is have a single model (that PowerPivotPro calls a "core") and multiple reports pointing at it (that they called "Thin"). The thins have a hidden slicer set to their department, that can't be changed on sharepoint. So, data refreshes are still just "refresh 1 report" (the core). Building all the thins can be a bit of a pain, and we sometimes write automation to help that.