Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ToddChitt
Super User
Super User

RLS not working (totally

I'm working with Power BI August Update and User Accounts in Azure AD, and want to limit access based on login. But it is not all that straight forward. Here's my security model:

 


UserName GroupName

======= ========

UserA        Group1

UserB        Group1

UserC        Group 2 

 

GroupName CustomerNumber
======== =======
Group1        CustomerX

Group1        CustomerY

Group2        CustomerZ

 

 I add these two tables to my model, then relate the first table to the second on GroupName, and the second table to my Customer table on CustomerNumber. Next, I set up a Role in Power BI Desktop model and add the following filter statement under the UserGroup table: [UserName] = USERNAME().

 

It does the filtering properly (filters the LIST of Customers, with their respective sales), but does not filter the TOTAL from the Sales table that is joined to Customer table. Sample table result:


UserName GroupName CustomerNumber TotalSales

====== ======== =========== ======

User1         Group1        CustomerX        $500

User1         Group1        CustomerY        $300

------------------------------------------------------

Total                                                $12,000,000

 

 (Note that the $12,000,000 is the sum total for ALL Customers. )

How do I get this to show the proper $800 ?

 

Thanks in advance




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





1 ACCEPTED SOLUTION

@ankitpatira:

Yes, a table visual was what I was representing there. All Relationships are activem, and I was summing a Calculated Column, not using a Measure.

I was under the impression that if you had a table structure that was totally One-To-Many (Example: One Salesperson to Many Territories; one Territory to many States, one State to many Customers) that all that would be needed was a simple filter on the top level of that structure, such as 'Salesperson'[UserName] = USERNAME(), and that would 'propogate' the security filters all the way down the line.

 

That may be the case, but for THIS model, there is a Many-to-Many relationship between Users and Customers. And that relationship goes through the GroupCustomer table.

 

To solve this, I INNER joined the first two data tables shown in my example, perserving only UserName and CustomerNumber, AND taking a DISTINCT:

  SELECT DISTINCT UserName, CustomerNumber

  FROM UserGroup INNER JOIN GroupCustomer

  ON UserGroup.GroupName = GroupCustomer.GroupName

and used that instead of the two tables.

 

Next, I put a DAX filter expression on Customer as follows:

CONTAINS ( 'SecurityQuery',
                     'SecurityQuery'[UserName],
                    USERNAME(),
                   'SecurityQuery'[CustomerNumber],
                   'Company'[CustomerNumber]
)

(Note: Credit goes to Marco Russo, Alberto Ferrari and Chris Webb from their book Microsoft SQL Server 2012 Analysis Services, The BISM Tabular Model; Chapter 15)

 

Now, when I 'View As Role' and select the one Security Role and specify a particular User, I see properly filtered list of Customers AND a correct Total for the Calculated Column.

 

(Side note: User Forums like this are GREAT. They help me solve my issues because more times than not it's only AFTER I have posted my question do I stumble across the answer on my own.)




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
djpirra
Helper III
Helper III

While this works on Import based models, this does not work on Composite models since you cannot use CONTAINS for RLS.

ankitpatira
Community Champion
Community Champion

@ToddChitt I can't seem to replicate your issue. Are you using table visual for the total or have a measure that is calculating total. Also make sure your relationships are active.

@ankitpatira:

Yes, a table visual was what I was representing there. All Relationships are activem, and I was summing a Calculated Column, not using a Measure.

I was under the impression that if you had a table structure that was totally One-To-Many (Example: One Salesperson to Many Territories; one Territory to many States, one State to many Customers) that all that would be needed was a simple filter on the top level of that structure, such as 'Salesperson'[UserName] = USERNAME(), and that would 'propogate' the security filters all the way down the line.

 

That may be the case, but for THIS model, there is a Many-to-Many relationship between Users and Customers. And that relationship goes through the GroupCustomer table.

 

To solve this, I INNER joined the first two data tables shown in my example, perserving only UserName and CustomerNumber, AND taking a DISTINCT:

  SELECT DISTINCT UserName, CustomerNumber

  FROM UserGroup INNER JOIN GroupCustomer

  ON UserGroup.GroupName = GroupCustomer.GroupName

and used that instead of the two tables.

 

Next, I put a DAX filter expression on Customer as follows:

CONTAINS ( 'SecurityQuery',
                     'SecurityQuery'[UserName],
                    USERNAME(),
                   'SecurityQuery'[CustomerNumber],
                   'Company'[CustomerNumber]
)

(Note: Credit goes to Marco Russo, Alberto Ferrari and Chris Webb from their book Microsoft SQL Server 2012 Analysis Services, The BISM Tabular Model; Chapter 15)

 

Now, when I 'View As Role' and select the one Security Role and specify a particular User, I see properly filtered list of Customers AND a correct Total for the Calculated Column.

 

(Side note: User Forums like this are GREAT. They help me solve my issues because more times than not it's only AFTER I have posted my question do I stumble across the answer on my own.)




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors