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
Setting Default Value of Slicer Checkbox based on role/column?
Hello Team, Its a custom requirement for me, I have a table with ntid and appid with Table 1: ntid appid [email protected] 83 [email protected] 52 [email protected] 72 [email protected] 25 Table 2: I have a mapping table appid and its desctiption for above table. Also i have created a relationship for both the tables. Based on the image attached below while loading the page by default , based on the user([email protected]) logged into the system and the role management is configured properly and that specific users application assigned need to be selected in the slicer check box by default and all the other check box(other user application assigned names(id)) should show with out selected in the screen of slicer. How this is possible to make in Power bi? Thanks and Regards, Siva
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, Abhiram
