Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
varung8899
Helper I
Helper I

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

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

GROUPCUSTIDPATH NAMEDEPARTMENTAREA
SGPRINTINGADMINADAMCPRINTING/07PRESSCALIFORNIA
SGPRINTINGUSEREVANSJPRINTING/02PRESSTEXAS
RNDISTRIUSERHUGOKDISTR/02DISTRIBUTIONFLORIDA
RNDISTRIADMINFREDCDISTR/07DISTRIBUTIONOHIO
POSALESUSERTIMORSALES/02SALESARKANSAS
KBPRODUCTUSEREVANSGPRODUCT/02PRODUCTILLINOIS
KBPRODUCTADMINHUGOKPRODUCT/07PRODUCTTEXAS
POSALESADMINSTEVEPSALES/07SALESILLINOIS
RNDISTRIADMINSTEVEPDISTR/07DISTRIBUTIONMICHIGAN

 

ACTUALS

 

DEPARTMENTAREAORDERSSALESMARGIN
DISTRIBUTIONMICHIGAN8504769013800
DISTRIBUTIONFLORIDA10006000025000
PRODUCTTEXAS5507500032000
SALESARKANSAS6507980036300
SALESILLINOIS4205400021000
PRESSCALIFORNIA3304300018500
PRESSTEXAS4805500022000
PRESSARKANSAS6507980036300
PRODUCTFLORIDA6306960040000

 

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. 

3 REPLIES 3
Daniel29195
Super User
Super User

@varung8899 

outout 

rls on ADMAC 

Daniel29195_0-1708871760527.png

 

rls on STEVEP 

Daniel29195_1-1708871792394.pngDaniel29195_2-1708871798343.png

 

MODEL

Daniel29195_3-1708871812534.png

 

 

 

 

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

Daniel29195_4-1708871878588.png

 

 

your dimusers link it to bootgroups  on email . 

 

 

and create a RLS :

Daniel29195_5-1708871957914.png

 

 

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

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
Super User
Super User

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.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.