Forum Discussion
RLS ROWS CONTAINING MULTIPLE VALUES SEPERATED BY DELIMITER AND FILTER DATA
Hi All,
I have a query regarding RLS. I have 3 tables USERTABLE, DIM_STORE & FACT_REVENUE. Structure of tables are below. I am trying to implement RLS so that a user can see only the Stores, Location, Unit & Brands assigned to his EMAIL which is calculated using USERPRINCIPALNAME(). In DIM_STORE table there are columns whose rows have multiple values separated by delimiter(;). I need users to see all the data assigned to them when they login and rows in DIM_STORE would be added frequently to limit the user access.
USERTABLE
ID | |
ABC | |
DEF |
DIM_STORE (RELATED TO USERTABLE VIA EMAIL)
STORE | LOCATION | UNIT | BRAND | |
1000;1500;2000 | 34E;77R;69J | DRINKS;CLOTHING | GATORADE;NIKE;PUMA | |
1000;2000 | 34E | CLOTHING | PUMA;NIKE |
FACT_REVENUE (I AM UNSURE HOW TO RELATE DUE TO MANY TO MANY RELATIONSHIP)
STORE | LOCATION | UNIT | BRAND | NET SALES | PROFIT | STOCK |
1000 | 34E | DRINKS | GATORADE | 10000 | 2000 | 15 |
1500 | 69J | CLOTHING | NIKE | 5000 | 3000 | 50 |
2000 | 69J | CLOTHING | PUMA | 6000 | 3200 | 75 |
1000 | 77R | DRINKS | GATORADE | 8000 | 4500 | 100 |
1500 | 34E | CLOTHING | PUMA | 3500 | 1000 | 35 |
2000 | 77R | DRINKS | GATORADE | 7000 | 3800 | 120 |
2000 | 34E | CLOTHING | NIKE | 10000 | 5000 | 150 |
1000 | 34E | CLOTHING | PUMA | 8500 | 4000 | 90 |
RESULT : WHEN user ABC LOGINS HE SHOULD BE ABLE TO SEE ALL THE DATA. WHEN user DEF LOGINS HE SHOULD BE ABLE TO SEE ONLY BELOW ROWS.
STORE | LOCATION | UNIT | BRAND | NET SALES | PROFIT | STOCK |
2000 | 34E | CLOTHING | NIKE | 10000 | 5000 | 150 |
1000 | 34E | CLOTHING | PUMA | 8500 | 4000 | 90 |
Please advise how I can achieve this using DAX or M query. Thanks in advance.
ex :
i have ALL for store .
this you need to do the following steps,
you need to convert all into a list of all store values seperated by "; "
in order for this to work, you need to have a dimstore .
steps
you need to add these steps at first, with the following m code :
Grouped = Table.Group(#"configtable (2)", {}, {"ConcatenatedColumn", each Text.Combine([STORE], "; ")}), ConcatenatedList = List.Accumulate(Grouped[ConcatenatedColumn], "", (state, current) => if state = "" then current else if Text.Start(state, 1) = ";" then Text.End(state, Text.Length(state) - 1) & current else state & "; " & current ), #"Replaced Value" = Table.ReplaceValue(Source,"ALL",ConcatenatedList,Replacer.ReplaceText,{"STORE"}),NB : remember to change the the name of the source for the next step after the replaced value step
to replaced value as shown in the image above
NB : this 3 steps, should be done for all 4 columns , ( so you need dimstore, dimlocation, dimunit and dimbrand )
these steps are done before splitting the columns into rows .
after these steps are done, you can then add the steps i have mentioned in my last reply .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
6 Replies
Hello varung8899
first go to power query
select the table dimstore ( this should be considered as a config table not dimstore , so you can have another dim store table )
now select column store, then under transform select split by columns ,
choose the delimiter --> ;
then in the afvanced options choose rows
do this to all other columns ( location, unit, brand )
now create a key in this table as follow : concatenate, store , location, unit and brand ( key1 ) ( before creating a key, make sure to change type of store column to text )
now create a key in the fact revenue concatenating : store, location, unit and brand . ( key2 ) ( also convert store to text )
link key 1 to key2 . and dimuser to the config table
now set the rls on the user email .
now use view role to test it : ( choose other use to inser the user email, and the rls you have created ( in my case the name of the RLS i have created is untitled )
this should work as per your requirement .
let me know if this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠Thank you very much Daniel29195 for your reply. This is awesome. I will try this out and let you know. I forgot to add one more thing. I will have entries for columns in DIM_STORE table as ALL. If any of the columns Store, location, UNIT, Brand has a value ALL then it should show all the values present in FACT_REVENUE for that particular column. Can this be met as well please on top of these steps ?
DIM_STORE (RELATED TO USERTABLE VIA EMAIL)
EMAIL
STORE
LOCATION
UNIT
BRAND
1000;1500;2000
34E;77R;69J
DRINKS;CLOTHING
GATORADE;NIKE;PUMA
1000;2000
34E
CLOTHING
PUMA;NIKE
ALL
34E;77R
CLOTHING
ALL
User GHI should be able to see All the Stores, 2 locations, only Clothing Unit & All Brands from Fact_Revenue. Is this also possible please ?
ex :
i have ALL for store .
this you need to do the following steps,
you need to convert all into a list of all store values seperated by "; "
in order for this to work, you need to have a dimstore .
steps
you need to add these steps at first, with the following m code :
Grouped = Table.Group(#"configtable (2)", {}, {"ConcatenatedColumn", each Text.Combine([STORE], "; ")}), ConcatenatedList = List.Accumulate(Grouped[ConcatenatedColumn], "", (state, current) => if state = "" then current else if Text.Start(state, 1) = ";" then Text.End(state, Text.Length(state) - 1) & current else state & "; " & current ), #"Replaced Value" = Table.ReplaceValue(Source,"ALL",ConcatenatedList,Replacer.ReplaceText,{"STORE"}),NB : remember to change the the name of the source for the next step after the replaced value step
to replaced value as shown in the image above
NB : this 3 steps, should be done for all 4 columns , ( so you need dimstore, dimlocation, dimunit and dimbrand )
these steps are done before splitting the columns into rows .
after these steps are done, you can then add the steps i have mentioned in my last reply .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Thank you for your legendary ideas to accomplish the requirements. I will take unique values from FACT_REVENUE and create another data table having only STORE, LOCATION, UNIT, BRAND, KEY columns removing duplicates using KEY.
