present the data in report according to organization roles?

[Jolyon]

Hi all,

have a question about integration of local data(Excel files) with PBI Desktop:

 I use Excel as data origin  and need to present the report in such a way,so that a Team Lead sees the data of all team, but the team members see only the data,that refer to them and not data of their colleagues.

As far as I know, it is not possible (with Excel).

Is there some workaround possible?

thanks a lot!

[v-qiuyu-msft · Accepted solution]

Hi @Jolyon,

It seems that you want team leader to see all the data and each team member only see their own data, right?

In your , the source table should contains a column which stores user's User Principal Name (UPN). This looks similar to an email address. See: Using the username() DAX function.

Then in Power BI desktop, you can create a role use UserName() like below:

Publish the report to Service, go to Dataset->Security, add team members under this role, each member view the report will only see his/her own data. As team leader is not under this role, he/her will view all data.

Best Regards,
Qiuyun Yu

[pghcraig]

While what the moderator says is technically true, it is also VERY deceptive.

Before you waste your time, does the team leader and the people under the team lead have a pro liscense?

Of course not!  Because no company is paying $10/month/employee on top of the existing Office365 subscription to only view a report.

If you put RLS on the data, they need a pro-liscense to actually view it.  I ran into this after wasting hours of my time, as well as pretty much everyone else who has tried this. 

The only way I have found to share the info without the filters being exposed to let people see other team members work is this:

1.  Make 1 master reprot filtered at report level by user name.  FIlter it for that staff member.

2.  Use "Save as" and save a duplicate copy of the report.  Then change the filter to the next employee.  Save. 

3.  Repeat for every person on a team.

Now the tricky part....  you will need to publish to web for each invidivual report and give each team member a link.  The web report will update about an hour after the data is refreshed.

Here is the ugly baby with this method.  The link can be shared and cannot be locked down.  This creates security concenrs (which I am wrestling with now).

I even went as far as to create a SharePoint page for each staff member, set security settings on the page so only that employee can view.   This works fairly well, except Microsoft thinks it's appropriate to not offer any way to disable the share links at this time.  If the source is embededded vs. giving out the URL, it's pretty safe.  Except they can Share to Linked In.... facebook, etc.

For as great as PowerBI is, it's achillies heel is this PRO liscense BS and lack of security without it just to consume content. 

I don't know what product manager genuius in Redmond thought this was going to drive subscriptions to PRO.... It won't.  This far and wide (read any threads on this) seems to be steering companies away from PowerBI because it becomes almost instantly cost prohibitive to use for what the majority of people want to use it for by requiring everyone in a company to have a $10/month subscription to consume a report. 

Every moderator seems to steer people to RLS, without mentioning...."Hey, you need to pay per person for that to work."

Like most people, I have a team who has perforamnce stats we want to share and automate.  These are not people who will ever do anything more than look at their weekly stats.  No one will pay for that privialge.  NO ONE.

What MS NEEDS to do, is allow a dashboard/report to be shared with the filters pane disabled.  Problem solved in the most simple and elegant manner.  Yes, you wouldn't need to save multiple versions of a report with a filter enabled, but it would be a one time deal and only done again as new staff onboards.  It's a highly suggested feature that is ignored for the sheer point of wanting to tell people to use RLS with a pro liscense.

[Jolyon]

Thank you, @v-qiuyu-msft!

I will test it next week. I also found this video to UPN, if someone needs:

https://powerbi.microsoft.com/en-us/blog/tech-tip-thursday-user-principal-name-upn-mapping-in-power-...

Regards,

[PavelR]

@Jolyon

Row level security should work.

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

Regards

Pavel

[Jolyon]

thanks @PavelR!I have not used it yet.

After reading the topic I still don't understand, how I should define the roles of team members, if I use RLS and imported data from Excel.

Should I have an extra Table in Power BI with team members?

and how should I define the role of team lead, so that he sees all the data?

Do you have probably some example where RLS is used?

Thanks a lot!

[PavelR]

@Jolyon you create roles as depicted in article and then you define the limitations to tables(data) by DAX.

No extra table needed at all.

Then in PBI service you put each members (or groups) to roles.

I would recommend to go step by step the article and you will completely understand how to use it.

Pavel