Dynamic RLS - DAX help needed
Hi! In PowerBI, we have a data model and want to apply a quite flexible data security that includes all possible scenario's. To achieve this we are thinking about using a UserSecurity table, which has all the permissions per user. See below a short example of a scenario: In this case, user 16 can have access to subscription 25 but only to ResourceGroup 15348 within subscription 25. At the same time, user 16 can see everything in Subscription 17. We want to use null (wildcard) because otherwise the UserSecurityTable can get too large. I tried to use relationships to the dimension tables, but in the above mentioned scenario, the null value overrides it. Therefore, our idea now is to filter the fact table instead since all the id's are already in there (probably will get performance issues, but will solve that later). Further there won't be a relationship between the tables since we have to create multiple relationships between the 2 tables. So, we need a DAX query to solve this in Manage Roles but my DAX is so far not good enough to make it work. In SQL, we would use the following query to achieve this: from FactTable1 fact join UserSecurity usc on ( (fact.subscriptionid = usc.subscriptionid or usc.subscriptionid is null) and (fact.resourcegroupid = usc.resourcegroupid or usc.resourcegroupid is null) ) where usc.userid = 16 Could someone please help me with translating this SQL query to DAX? And I'm happy to receive some other idea's on how to set this data security up! Many thanks! Kind regards, Niels
RLS based on 3 columns (All possible combinations)
Hi All, Looking to implement RLS that is based on 3 columns. So all possible combinations must be taken in to account while filtering. Consider the below table, the user could fall in either: - None of the columns, OR - Any 1 column, OR - Any 2 columns, OR - All 3 columns I am using an excel to capture the role against email IDs but can't crack the DAX yet in Manage Roles, any leads with the DAX will be really helpful.
Help on RLS in Power BI
Hi Team - We have tabular model (screenshot below) and enabled RLS on the "DimSubsidiary" table, which is applying security filter to the "FactSales" tables. However, we have a requirement to show sales different when slicer is placed on Geography. Example: As per Sample Data below, User A is allowed to see US and UK Subsidiary Data For User A: Total Sales (Subsidiary=US) = 100 + 600 ( Row 1 + Row 2) . This is working as expected. Total Sales (For Geo=US) = 100 ( because of RLS user A can see Row 1 and Row 2) and For US, it's 100. but User A has access to US subsidary , they should be able to see Revenue for US Geography i.e they should see sales as ( 100 (Row1) + 500 (Row 3) + 700 (Row 5) Beacuse of RLS in place this will not work Can you please suggest how to proceed further in order to show 1300 as sales for User A when we select Geo="US" ? Do I need to create any bridge table based of Fact? Data Model Thanks, AbhiramOption to Disable/Bypass the RLS on active relationship
Hi Team - We have tabular model (screenshot below) and enabled RLS on the "DimCustomer" table, which is currently security applying the filter to both the "FactSales" and "FactCompute" tables. However, we have a requirement to disable RLS between the "DimCustomer" and "FactCompute" tables, while still keeping it enabled for the "FactSales" table. Upon reviewing the available solutions, I have come across below approaches, but it has certain disadvantages. Please suggest if there any other alternative options 1) Implement RLS on FactTable Implement RLS on FactSales Table Only Disadvantage: FactSales is very big , it occupies 70 GB in our data model. I think implementing RLS on Fact will slow down the performance. 2) Clone the Customer Table Have two set of Customer Table and enable RLS on One Customer Table. Disadvantage: If we want to show Sales and Cores by customerName in single chart then we will end up with customerName from different tables RLS Code On DimCustomer: VAR CUSTOMERS = CALCULATETABLE ( VALUES ( 'DimUser'[SubsidiaryID] ), 'DimUser'[UserAlias] = USERPRINCIPALNAME () ) RETURN 'DimCustomer'[CustomerID] IN CUSTOMERS Thanks, AbhiramRLS DAX Query Optimization
Hi Team, I have implemented RLS on below model but the performance is slow. can some please suggest way to optimize DAX Code. RLS Tables: User Product Sub: A User can have access to atleast one products. ( Each User will have atleast one or more entry in Table) User Product Sub WW : User has access different categories of Products. ( Each User will have single entry in table) Fact Sales: Sales Reported for each Customer. Due to Telemetry, we see null Customer ID in Facts. We want to show sales for products even for null customers only for user who are part of Product Sub WW. Product Table: Product ID and Product Name RLS DAX code on Product Table: VAR PRODUCTS = CALCULATETABLE ( VALUES ( 'User Product Sub'[ProductID] ), 'User Product Sub'[UserAlias] = USERPRINCIPALNAME () && 'User Product Sub'[Is Authorized User] = TRUE() ) VAR WWAccess = MAXX(FILTER('User Product Sub WW', 'User Product Sub WW'[UserAlias] = USERPRINCIPALNAME () && 'User Product Sub WW'[Is Authorized WW User] = TRUE), 1) RETURN 'Product'[ProductID] IN PRODUCTS || ('Product'[ProductID] = BLANK() && WWAccess =1) Please find the attached model with sample data. Sample Data: Thanks, Abhiram
Dynamic RLS with complex security module
Hi, I'm trying to create a Dynamic RLS for a complex security module, here are the table structure and relationship: Table 1. Users Username Company Code Role Type Access ID A A S B A A C A R 77 D B B Table 2. Roles Username Access Detail A 12345 A 67891 C 77 D 97 Table 3. Data ID Date Value Company Code Role S Role B Role R 1 01-01-2021 1 A 12345 77 2 01-01-2021 2 A 67891 3 01-05-2021 3 A 77 4 10-01-2021 4 B 97 As it's showing in the above Data Table, User "A" should be able to see the first 2 rows as Role S 12345 & 67891 are linked to user A in Roles Table and user A is in Company "A", User “C” should be able to see row 1 and row 3 as Role R is 77 and 77 is linked to user C and the user is in Company “A”, and finally User “D” should be able to see only row 4 as Role B is 97 and user Company is “B”. User “B” should be able to see everything as Role Type “A” is Admin. So, the relationship here is: Table 1 (Username) -> Table 2 (Username) Table 1 (Company Code) -> Table 3 (Company Code) Table 1 (Role Type) -> Table 3 (Role S, Role B or Role R) Table 2 (Access Detail) -> Table 3 Value of (Role S, B or R) I’ve tried different type of DAX expression but with no luck so far, any ideas that might help in resolving this? Thanks in advance,
