1

In Power BI, have two tables: one for UsersData and one for CountryData. The Username and Country columns can be identified in the UsersData table and the Country and CityName columns are in the CountryData table. The CountryData and UsersData tables are connected by using Country. Visualize the Country name and city in a table in the Power report view.

enter image description here

Requirement: When a user logs into the power bi service, he wants to see only the data related to the user. I looking for any suggestions to implement this scenario without using RLS.

Ramesh Madhubhashitha
  • 13
  • 4
  • Why RLS can't be used? It is the right tool, designed for this task. – Andrey Nikolov Jul 18 '23 at 06:17
  • RLS (Row level security) is commonly used to restrict data access for given users. However, in this case, I am only contemplating data filtering based on the user. So, in theory, is it appropriate to use the Power BI RLS option in this scenario? – Ramesh Madhubhashitha Jul 18 '23 at 19:27

1 Answers1

0

Without RLS you can write a measure based on the current user, eg

IsCurrentUser = if(USERPRINCIPALNAME() = SELECTEDVALUE(Users[User]),1,0)

and use that to filter a visual.

enter image description here

And the Chiclet Slicer supports "Forced Selection" so if you filter the Chicklet Slicer down to a single item, it will be automatically selected.

The tables and relationships would be set up the same way you would for RLS, eg

enter image description here

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks for your answer. That will be the solution to achieve my requirement. but I am looking for some approach to filter the data from the table level (I mean tables that include in Data view and Model view) without using RLS. As an example, I want to create a new table called **UserCities**, its data needs to be filtered by the user who logs into the power bi service. – Ramesh Madhubhashitha Jul 18 '23 at 19:07
  • I tried the following query, but it gives some errors. UserCities = CALCULATETABLE( DISTINCT( SELECTCOLUMNS('Countries', "Country", 'Countries'[Country], "City", 'Countries'[CityName] ) ), Users[UserName] = USERPRINCIPALNAME() ) – Ramesh Madhubhashitha Jul 18 '23 at 19:08