Forum Discussion
Helper IIFilter values from one table in another without relationship DAX
Hi talespin Please see the solution below. As you mentioned earlier creating a measure using the formula wasn't the right way and creating a role and applying RLS was the right approach. Since no one was able to provide a RLS DAX query for this requirement we have itself come up with a DAX solution to achieve the requirement. Please see 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 TABLE
AND 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
Helper IIThanks for your valuable guidance talespin . May I pls have a final ask to close this thread. If I have a column called GROUP in DIM_ACT having different group names for each user & multiple users can be tagged to the same group in future, could you please let me know what will be your approach to tweak the above measure and create a role in RLS ? Appreciate your perseverance. TIA.
Solution Sagehi varung8899
What you are asking is more of RLS and Data Model question and not DAX. If you have your Data Model right, you do not/should not need this DAX to filter data for RLS. Think/Take help on how should you design your data model in this case.
- varung8899
I hear you talespin . The main reason why I went for DAX is because of M:M relationship between columns in my tables and I did not have another option than to go ahead with this approach in such a short time.
- talespin
hi varung8899
Please check this file, try to do something like this, there is probably better way to implement this, this is just to explain what I mentioned above.
https://drive.google.com/file/d/1Q_5OnCyLAgNW6soM5L1ek0o2kfQw45wE/view?usp=sharing
Viewing it as [email protected] shows me only data that this user is meant to see. I have hard coded this user in roles filter as I do not have personal PBI service account. This is just a demo to guide you in right direction.
Data Model
- varung8899
Hi talespin Please see the solution below. As you mentioned earlier creating a measure using the formula wasn't the right way and creating a role and applying RLS was the right approach. Since no one was able to provide a RLS DAX query for this requirement we have itself come up with a DAX solution to achieve the requirement. Please see 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 TABLE
AND 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