After Applying Row Level security I had to make a few changes to the interactions between tables, but everything worked fine.
However, once I published to the service a dropdown filled with portfolio names suddenly showed ALL available report names.
Row level security still hid MOST of the data, resulting in errors in the visuals when selected, but not all.
It seems the service (app.powerbi.com) doesn't quite handle the row level security the same way as the desktop.
Because of this, I added a filter on to the drop down to remove the additional rows from the drop down so that the users couldn't select items they were not supposed to see as a temporary measure.
This has mostly worked, though I would prefer not to have this filter on and the row level security to work as it does in the desktop app.
What's more, one user cannot see anything in the dropdown with the filter on (even though I've checked how his user principal etc is showing and it is all fine)
Some of the way we set things up originally worked fine until we applied row level security, after that it didn't like some of the joins and the like so we have made a few changes, removed joins and added measures as filters and the like.
Some information about the set up:
Table - Client Details: Central Distinct List of clients, portfolios, ids etc
Used in Drop Down
Joins - 1-> many across most tables to filter by Portfolio Id
1 join BI-directional to "User Portfolios" which is used in row level security
Due to Drop down not filtering out portfolios in web service added a measure as a filter (detailed below)
Table - User Permissions: UserEmail (w/o suffix) and Portfolio Id
Has row level security applied to it:
UPPER([EmailNoSuffix]) = UPPER(Left(USERPRINCIPALNAME(),Search("@",USERPRINCIPALNAME(),1)))
In desktop mode this filters all tables and drop downs
As it does not seem to work when published I also added this:
PortfolioFilterNoSuffix = CALCULATE(COUNT(UserPermissions[EmailNoSuffix]), FILTER(UserPermissions,UPPER(UserPermissions[EmailNoSuffix]) = UPPER([UserEmailNoSuffix])))
And then the clientdetails drop down was filtered by > 0, however, I have just tried "not blank" to see if it resolves the user issue (pending response)
Table - DistinctDates: Distinct list of dates across reporting tables
Joins 1 to Many to Reporting Tables (by date)
Joins both directions to ->
Table - DistinctDatesClient: - Distinct list of Dates per client
Date used in drop down
Measure - DateFilterMeasure = CALCULATE(COUNT([DateWithLatest]), Filter(DistinctDatesClient,DistinctDatesClient[Portfolio_FulcrumEntityId]=SELECTEDVALUE(ClientDetails[Portfolio_FulcrumEntityId])))
^this resticts dates based on client selection. Originally this was joined to the client details table directly but had to change due to row level security.
The reporting tables are end points filtered by the portfolio id and date selected.
Anyway - any advice on how to get round this issue would be great - I am sure there are some better ways of doing things than I have done them, or there is an issue with the web service - either way, would love some input. Cheers!
