Dynamic RLS filtering rows based on multiple columns in table and user belonging to multiple groups

[varung8899]

Hi Fellow Experts,

Its Friday and you must be very excited. Hope you would be able to assist me solving this simple RLS query. We have a department who is planning to configure dynamic RLS in Power BI using an existing static table which they maintain for controlling other application access. The table BOOTGROUPS is shown below.  A user belongs to only 1 group currently but would belong to multiple groups in future. The DAX query must be able to define the filter using [column names] and NOT static values (DEPARTMENT ="PRESS") etc instead match the values for user available in BOOTGROUPS to IN [DEPARTMENT] column. Hence we would need to create a role using DAX for both scenarios please (if multi group solution can be used for single, cool). Can this be done by defining a single RLS group since we already have the group names in table please ? Kindly provide a solution. Assume we have another table CUSTIDEMAIL containing their CUSTID & EMAIL for matching USERPRINCIPALNAME() in DAX. 

BOOTGROUPS

GROUP	CUSTID	PATH NAME	DEPARTMENT	AREA
SGPRINTINGADMIN	ADAMC	PRINTING/07	PRESS	CALIFORNIA
SGPRINTINGUSER	EVANSJ	PRINTING/02	PRESS	TEXAS
RNDISTRIUSER	HUGOK	DISTR/02	DISTRIBUTION	FLORIDA
RNDISTRIADMIN	FREDC	DISTR/07	DISTRIBUTION	OHIO
POSALESUSER	TIMOR	SALES/02	SALES	ARKANSAS
KBPRODUCTUSER	EVANSG	PRODUCT/02	PRODUCT	ILLINOIS
KBPRODUCTADMIN	HUGOK	PRODUCT/07	PRODUCT	TEXAS
POSALESADMIN	STEVEP	SALES/07	SALES	ILLINOIS
RNDISTRIADMIN	STEVEP	DISTR/07	DISTRIBUTION	MICHIGAN

ACTUALS

DEPARTMENT	AREA	ORDERS	SALES	MARGIN
DISTRIBUTION	MICHIGAN	850	47690	13800
DISTRIBUTION	FLORIDA	1000	60000	25000
PRODUCT	TEXAS	550	75000	32000
SALES	ARKANSAS	650	79800	36300
SALES	ILLINOIS	420	54000	21000
PRESS	CALIFORNIA	330	43000	18500
PRESS	TEXAS	480	55000	22000
PRESS	ARKANSAS	650	79800	36300
PRODUCT	FLORIDA	630	69600	40000

When user ADAMC logins he should see only records related to PRESS & CALIFORNIA whereas STEVEP should be able to see SALES, DISTRIBUTION, ILLINOIS, MICHIGAN. There are other columns in BOOTGROUPS & other FACT tables like CITY, SOURCE, DEST etc- and I need to be able to expand the DAX query to filter these in future. Thank you very much. Have a fun filled weekend. TIA. 

[Daniel29195]

@varung8899 

outout 

rls on ADMAC 

rls on STEVEP 

MODEL

create a column in both tables :  actuals and bootgroups  (  the link between these two tables could be many to many -and that is ok ) 

your dimusers link it to bootgroups  on email . 

and create a RLS :

and you are good to go  .

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! 🤠

[varung8899]

Hello Daniel, Groups are basically the AD group names which they are maintaining in the table. It is not a combination of Department & Area. SGPRINTINGADMIN group can be tagged to SALES department & area TEXAS as well. Multiple users can belong to the same groups and a user can belong to multiple groups. Same groups can exist for different area also. I don't think above solution will work for mentioned scenario ?

[_AAndrade]

Hi,

Take a look at this blog: https://radacad.com/dynamic-row-level-security-with-profiles-and-users-in-power-bi

I'd to do something similar and this blog helped me.