Filtering Multiple groups & columns in Dynamic RLS

[varung8899]

Hi All,

I would like to use below table DIM_ACT as access restriction table for users. This is just a sample table and it will have many rows. DIM_USER table contains Email & ID for applying USERPRINCIPALNAME() DAX.  FACT_ACTUALS table has all the below columns plus SALES, QUANTITY & STOCK without value ALL in any of the columns. If a column contains "ALL" means there should not be any filter applied to that column when RLS is applied. I have split the columns using delimiters as well and after transformation the below DIM_ACT table has more rows. Eg: There is no row having Level 100;A20;K40 currently. It has 100 A20 & K40 as separate rows instead similarly for all other columns where ";" is contained. DIM_ACT table has many to many relationship with FACT_ACTUALS with all the below columns (Level, Label,Source, CC, ORG, SITE). Only Level column is in Active relationship others are Inactive. Multiple ID's can belong to same group and multiple groups can belong to same user hence same Path can have same group access with different user ID's & different values in remaining columns. Path will be separate visuals in report (Flex for you & Sample WS will be 2 separate visuals and it will have access defined according to DIM_ACT after applying RLS. Can you pls propose a solution wherein I can filter the values dynamically in FACT when RLS is applied in Path, Level, Label, Source, CC, ORG, LOC columns in DIM_ACT table. Not sure if its possible due to inactive Many to Many relationship.

DIM_ACT TABLE

PATH	GROUP	ID	LEVEL	LABEL	SOURCE	CC	ORG	LOC
FLEX FOR YOU	BASIC A	SDK	100;A20;K40	XKG;TBF	1	E250;V365	25;36	ALL
SAMPLE WS	BASIC A	SDK	100;A20;K40	XKG;TBF	1	E250;V365	25;36	ALL
FLEX FOR YOU	INTER A	REN	100;A20;K40	XKG;TBF	ALL	E250;V365	25;36	ALL
SAMPLE WS	INTER A	REN	100;A20;K40	XKG;TBF	ALL	E250;V365	25;36	ALL
FLEX FOR YOU	ADVANCED A	OBQ	ALL	ALL	ALL	E250;V365	25;36	ALL
SAMPLE WS	ADVANCED A	OBQ	ALL	ALL	ALL	ALL	ALL	ALL
FLEX FOR YOU	ADVANCED B	ABC	ALL	ALL	2	E250;V365	ALL	ALL
FLEX FOR YOU	INTER B	XYZ	300:J99	TUT	2	ALL	58	ALL
FLEX FOR YOU	BASIC B	DEF	J99	TUT	2	20	ALL	800
SAMPLE WS	INTER B	XYZ	300:J99	XKG	ALL	35A;40B	35;40	200;500
SAMPLE WS	ADVANCED B	ABC	F350	ALL	2	ALL	ALL	ALL
SAMPLE WS	BASIC B	DEF	300	DUC	2	ALL	45	300;450

TIA. 

[varung8899 · Accepted solution]

Please see the solution below.

1.) All semi-colon values in DIM_ACT columns must be replaced by "|" symbol for this formula to work. Either change the source data or create a calculated column to replace ";" with "|".

2.) Eliminated DIM_USER table and brought in EMAIL ID column to DIM_ACT table itself. 3.) There is only 1 record for a single user in DIM_ACT under a single GROUP.

4.) Create a ROLE in Manage roles and apply below DAX under FACT_ACTUALS table. Using the below DAX , when the user logs in he will be able to see only the values separated using "|" in the respective columns of DIM_ACT and when ALL is present in any of the columns, he will be able to see all the values available in FACT ACTUALS.   

/* FILTER DIM_ACT TO FETCH THE RECORD HAVING ONLY THE LOGGED IN USER EMAIL ID & VALUES ASSIGNED UNDER LEVEL COLUMN */

&& [LEVEL] IN

(

VAR _LEV = MAXX 

(FILTER( 'DIM_ACT',[EMAIL ID] = USERPRINCIPALNAME () ),'DIM_ACT'[_LEV])

/* TAKE THE PATH LENGTH OF LEVEL COLUMN. THIS IS DONE BECAUSE THERE COULD BE N NUMBER OF VALUES SEPARATED BY | SYMBOL IN DIM_ACT TABLEAND WE NEED TO SEPARATE THEM AS INDIVIDUAL VALUES */

VAR _LEVLEN = PATHLENGTH (_LEV )

/* CREATE A VIRTUAL TABLE WITH COLUMN NAME LEVLIST HAVING THE LIST OF LEVEL COLUMN VALUES */

VAR _LEVTABLE = ADDCOLUMNS ( GENERATESERIES ( 1, _LEVLEN ), "LEVLIST", PATHITEM ( _LEV, [Value] ) )

/* SELECT THE COLUMN VALUES AND PASS THE VALUES TO LIST */

VAR _LEV_list = SELECTCOLUMNS ( _LEVTABLE, "LEVEL", [LEVLIST] )

/* ADD ALL THE LEVEL VALUES FROM FACT_ACTUALS. THIS IS FOR A USER WHO HAS VALUE "ALL" ASSIGNED TO HIM UNDER LEVEL IN DIM_ACT TABLE */

VAR _ALLFACTLEV = ADDCOLUMNS ( VALUES ( FACT_ACTUALS[LEVEL] ), "ALL", "ALL" )

/* RETURN ONLY THE VALUES ASSIGNED IN LEVEL COLUMN IN DIM_ACT FROM FACT TABLE FOR LOGGED IN USER OR RETURN ALL THE VALUES FROM FACT TABLE WHEN THE LEVEL COLUMN IS PROVIDED AS ALL */

RETURN

SELECTCOLUMNS(FILTER ( _ALLFACTLEV, [LEVEL] IN _LEV_LIST || [ALL] IN _LEV_LIST ),[LEVEL]))

&& [LABEL] IN 

(

VAR _LAB = MAXX 

(FILTER ( 'DIM_ACT', [EMAIL ID] = USERPRINCIPALNAME () ),'DIM_ACT'[LABEL])

VAR _LABLEN = PATHLENGTH ( _LAB )

VAR _LABTABLE = ADDCOLUMNS ( GENERATESERIES ( 1, _LABLEN ), "LABLIST", PATHITEM ( _LAB, [Value] ) )

VAR _LAB_LIST = SELECTCOLUMNS ( _LABTABLE, "LABEL", [LABLIST] )

VAR _ALLFACTLAB = ADDCOLUMNS ( VALUES ( FACT_ACTUALS[LABEL] ), "ALL", "ALL" )

RETURN

SELECTCOLUMNS (FILTER ( _ALLFACTLAB, [LABEL] IN _LAB_LIST || [ALL] IN _LAB_LIST ),[LABEL]))

&& [SOURCE] IN ------ Similarly repeat the above code for other columns in DIM_ACT

[varung8899]

Using the ID field. I have now created separate dimension tables for Level (DIM_LEVEL), Label(DIM_LABEL), DIM_source, DIM_cc, DIM_org, DIM_loc which contains single column respectively in all these tables having unique values establishing 1 to many relation with Fact.

• It would be great if I could use a single role using DAX query and not use any relations between Actuals & Fact. From performance point.of view, it would be better if the solution can be achieved using the above DIM_ACT table format as when I am transforming the rows using Split delimiters the table could become very big in future. This table rows will be added/updated by departments going forward for new user access. Please let me know for any queries.

[varung8899]

@lbendlin Do you have any ideas please ?

[lbendlin]

How is your DIM_USER table connected to the DIM_ACT table?