Reporting on Degenerate dimension from 2 fact tables with a dimension in between
Hello, I have 2 fact tables (user, sales) and a dimension table (loaction) in between. Now I need to report on a degenrate dimension on one of the fact table (called brand in sales), a measure on the same fact table (count rows in sales) and a measure on the second fact table (count of related users). Desired table is what I am getting when I join the dimesnions and metrics together. However as you cab see the user count is the same for all brands. But I really would like to see is what are the count of users for those locations from where the brands were purchased. So I would like something like this. As you can see adidas was bought frpom location 1 which has only 2 users. I cant create a many to many relation between users and sales as I have a RLS set up using profile and staff. Is there any way I can solve this. Please please please help. johnt75 , MFelix , OwenAuger Regards, Meena
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
Row Level Security with Multiple levels (Complicated)
Hi, I am looking to implement RLS for multiple levels. I have 2 tables as below. 1) Groups 2) Tasks Groups table will have , Group name and Member email. And tasks will have below columns Upon login by the user i want to filter my report in below ways. Rule: 1) all the records should be visible wherer Isrestricted = NO for every user. Rule 2: 2) Up on login by User, Rule 1 above should be applied and also, Isrestricted group = Yes and it should look at group table who is the user and how many groups the user is part of and only those groups in Tasks table should be filtered. Rule 3: 3) Irrespective of above, who ever part of submitted by in the tasks table they have to see all the submitted by records along with Isrestricted access = NO. I have tried relaly very hard to crack this and finally coming here for help from great minds. Attaching the PBIX for your perusal. Appriciate your help guys. https://1drv.ms/u/s!ApY7Qk9reuaNiw9L76Qr3dkIwHC6?e=LAe1al Pragati11 amitchandak ValtteriN Greg_Deckler GilbertQ MikeJohnsonZA AlexisOlson
