Filter Power BI report based on current user

Question

We're currently developing a Power BI Dashboard (Office 365) for our company and would like to tailor the information on the dashboard based on the current user's preferences.

Our company has multiple departments and sub-departments, so to display every department's figures to all users would be counter-productive.

For example if Bob is in Sales for Europe - he'll only see European sales, while Sue will only see Sales for America.

Is there a way PowerBI can identify the current user and then filter the results from the SSAS cube based on the user's preferences (which are store on a SQL table)?

I would imagine this could be done via the M Query statement, but I'm unsure on how to use it to filter the results.

Answer 1

It sounds like you have an SSAS cube already? Multidimensional? First setup role based security in SSAS. If you have SQL tables that define who should see what (or in your case it sounds like a user preference, so who wants to see what) then use dynamic security: http://hccmsbi.blogspot.com/2007/08/implementing-user-specific-security-in.html

Then install the Power BI Enterprise Gateway: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/

Then connect it to SSAS: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-manage-ssas/

You will need SQL Server 2012 SP1 CU4 or later on SSAS. You will need Enterprise or BI Edition SSAS. And you will need a Power BI Pro license for each user.