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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gazzo1967
Helper III
Helper III

RLS/Dynamic Filter Help needed

HI All
Sample data
Table One (Employee TEST data)

NAMEEMAILLOCATIONWATCHROLE
Andy JonesAndy.Jones@wm.netAredCrew
Paul SmithPaul.Smith@wm.netBgreenWatch
Dave BrownDave.Brown@wm.netCwhiteCrew
Rich DavisRich.Davis@wm.netDblueCrew



Table Two(test Data)

NAMEEMAILLOCATIONWATCHROLEassessment
Andy JonesAndy.Jones@wm.netAredCrew90
Paul SmithPaul.Smith@wm.netBgreenWatch79
Dave BrownDave.Brown@wm.netCwhiteCrew89
Rich DavisRich.Davis@wm.netDblueCrew76
Steve BlueSteve.Blue@wm.netDblueFF80
Mike NewMike.New@wm.netBgreenFF78
Pete GreenPete.Green@wm.netBgreenFF76
Mike WhiteMike.White@wm.netAredFF69


My relationship between the 2 tables is on the email addresses

Im trying to create Dynamic RLS so that for example

If Paul Smith logs in  they will see

Table Two(test Data)

NAMEEMAILLOCATIONWATCHROLEassessment
Paul SmithPaul.Smith@wm.netBgreenWatch79
Mike NewMike.New@wm.netBgreenFF78
Pete GreenPete.Green@wm.netBgreenFF76


If Rich Davis  logs in  they will see

Table Two(test Data)

NAMEEMAILLOCATIONWATCHROLEassessment
Rich DavisRich.Davis@wm.netDblueCrew76
Steve BlueSteve.Blue@wm.netDblueFF80


I have tried using RELATED but it tells me one of my columsn doesn't exist even though it does

I have tried many variations and my head hurts 😞
Any help welcome

Gary 🙂

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

You don't have the two table connected together do you?  There is no need for a relationship between the two tables.  That is the only thing I can think of that might cause the problem.

View solution in original post

14 REPLIES 14
jdbuchanan71
Super User
Super User

You don't have the two table connected together do you?  There is no need for a relationship between the two tables.  That is the only thing I can think of that might cause the problem.

@jdbuchanan71 
Thank you soo much for your patience and support 🙂
Yep it was the relationship.
Just for future reference can a third table ie SA2 be introduced (Just a question 😁)

Certainly.  If the new table is linked below the SA Table (one to many from SA to SA2) then the filter you have on SA will get applied.  If it is separate from SA then you would just add another filter to the new table in the same role.

@jdbuchanan71 
Again thank you for your support 
🙂
Gary

jdbuchanan71
Super User
Super User

Gotcha, that makes more sense.  I am going to assume you don't want users to see anything from the 'WMFSED Table', this is the table that holds their email and location / job_watch so there is nothing for them to look at anyway.

We apply a filter to the 'SA Table' like this.

VAR _UPN = USERPRINCIPALNAME()
VAR _Location = LOOKUPVALUE ( 'WMFSED Table'[JOB_LOCATION],'WMFSED Table'[WORK_EMAIL],_UPN)
VAR _JobWatch = LOOKUPVALUE ( 'WMFSED Table'[JOB_WATCH],'WMFSED Table'[WORK_EMAIL],_UPN)
RETURN
'SA Table'[location] = _Location &&
'SA Table'[job_watch] = _JobWatch

This reads the 'WMFSED Table'[JOB_LOCATION] and 'WMFSED Table'[JOB_WATCH] based on who is logged in and applies those as filters to teh 'SA Table'

We also apply a filter to the 'WMFSED Table' like this.

FALSE()

This just means that the role cannot see any data on this table.

Using the View as Andy.Jones we can see the filtering applied:

jdbuchanan71_1-1696967301556.png

 

And this is what Pete.Green looks like:

jdbuchanan71_2-1696967385516.png

 

I have attached my updated file for you to look at.

 

 

 

@jdbuchanan71 
I'm really sorry if this is being problematic but for obvious reasons were using test data but when applied to my live stuff it fails to work as expected.

As you can you to understand i can't show any live contact data so the email address, names etc need to be assumed 😞
And with the amount of records its difficult to show.

But i have applied your formula

The data return is only returning the logged in user detail whose email address I have used to test

gazzo1967_0-1696978721209.png


So do I need to include the other columns in the expression? (all from the SA Table)
The other 'people' at the location and watch names are not showing 😞
regards Gary

jdbuchanan71
Super User
Super User

If you are trying to apply filters to multiple tables you would have to write rule for each one in your Role.  If there was a Table3 in the example below and you wanted to apply a filter to it as well you would have to select table3 and put the DAX filter code in.  You can't filter Table3 with code that is applied to Table2.

jdbuchanan71_0-1696963499018.png

 

@jdbuchanan71 
There is only 2 tables
the part about adding other columns means from either of the 2 tables in the expression.

Dont know if you got to see this post

POST

Sorry will try to better explain my objective

When a user 'Peter Green' logs in I would like him to only see from SA table
Anyone who is at the same [location] and  [job_watch] 

 

Sorry I have a much clearer head today!

 


Expected outcome

full_nameemail_addresslocationjob_watchrole_basicassessment
Mike NewMike.New@wm.netBgreenFF78
Pete GreenPete.Green@wm.netBgreenFF76


When a user 'Andy Jones' logs in I would like him to only see from SA table

full_nameemail_addresslocationjob_watchrole_basicassessment
Andy JonesAndy.Jones@wm.netAredCrew90
Mike WhiteMike.White@wm.netAredFF69

 



SA Table

full_nameemail_addresslocationjob_watchrole_basicassessment
Andy JonesAndy.Jones@wm.netAredCrew90
Paul SmithPaul.Smith@wm.netBwhiteWatch79
Dave BrownDave.Brown@wm.netCwhiteCrew89
Rich DavisRich.Davis@wm.netDblueCrew76
Steve BlueSteve.Blue@wm.netDblueFF80
Mike NewMike.New@wm.netBgreenFF78
Pete GreenPete.Green@wm.netBgreenFF76
Mike WhiteMike.White@wm.netAredFF69
Peter LongPeter.Long@wm.netBblueFF69
Mitchell BowMitchell.Bow@wm.netBredFF69

 

WMFSED table

NAMEWORK_EMAILJOB_LOCATIONJOB_WATCHRole_Basic
Andy JonesAndy.Jones@wm.netAredCrew
Paul SmithPaul.Smith@wm.netBwhiteWatch
Dave BrownDave.Brown@wm.netCwhiteCrew
Rich DavisRich.Davis@wm.netDblueCrew
Steve BlueSteve.Blue@wm.netDblueFF
Mike NewMike.New@wm.netBgreenFF
Pete GreenPete.Green@wm.netBgreenFF
Mike WhiteMike.White@wm.netAredFF
Peter LongPeter.Long@wm.netBblueFF
Mitchell BowMitchell.Bow@wm.netBredFF



gazzo1967
Helper III
Helper III

@jdbuchanan71 
Apparently my post need moderator approval 😞

 

gazzo1967
Helper III
Helper III

@jdbuchanan71 
Sorry will try to better explain my objective

When a user 'Peter Green' logs in I would like him to only see from SA table
Anyone who is at the same [location] and  [job_watch] (can i add data from either table as additional columns? I havent added all the columns on here)

 

Sorry I have a much clearer head today!

 


Expected outcome

full_nameemail_addresslocationjob_watchrole_basicassessment
Mike NewMike.New@wm.netBgreenFF78
Pete GreenPete.Green@wm.netBgreenFF76


When a user 'Andy Jones' logs in I would like him to only see from SA table

full_nameemail_addresslocationjob_watchrole_basicassessment
Andy JonesAndy.Jones@wm.netAredCrew90
Mike WhiteMike.White@wm.netAredFF69

 



SA Table

full_nameemail_addresslocationjob_watchrole_basicassessment
Andy JonesAndy.Jones@wm.netAredCrew90
Paul SmithPaul.Smith@wm.netBwhiteWatch79
Dave BrownDave.Brown@wm.netCwhiteCrew89
Rich DavisRich.Davis@wm.netDblueCrew76
Steve BlueSteve.Blue@wm.netDblueFF80
Mike NewMike.New@wm.netBgreenFF78
Pete GreenPete.Green@wm.netBgreenFF76
Mike WhiteMike.White@wm.netAredFF69
Peter LongPeter.Long@wm.netBblueFF69
Mitchell BowMitchell.Bow@wm.netBredFF69

 

WMFSED table

NAMEWORK_EMAILJOB_LOCATIONJOB_WATCHRole_Basic
Andy JonesAndy.Jones@wm.netAredCrew
Paul SmithPaul.Smith@wm.netBwhiteWatch
Dave BrownDave.Brown@wm.netCwhiteCrew
Rich DavisRich.Davis@wm.netDblueCrew
Steve BlueSteve.Blue@wm.netDblueFF
Mike NewMike.New@wm.netBgreenFF
Pete GreenPete.Green@wm.netBgreenFF
Mike WhiteMike.White@wm.netAredFF
Peter LongPeter.Long@wm.netBblueFF
Mitchell BowMitchell.Bow@wm.netBredFF

 

jdbuchanan71
Super User
Super User

I'm not sure what you are saying.  There is a 3rd table involved and you are trying to filter that one as well?

Please share sample data from each table with the real table names rather than "table1 and table2" and descibe the filtering you are trying to apply to each table.

gazzo1967
Helper III
Helper III

@jdbuchanan71 
It appears that the LOOKUP isnt working 😞
when I add the SA[location] to a table on a seperate page and apply the modelling the column filtering of the data does not change

jdbuchanan71
Super User
Super User

You don't need to have Table1 linked to Table2.  Table1 is there just to get the employee and what region they can see.  You use that information to filter Table2.

Create a role with a filter on Table2 like this.

VAR _UPN = USERPRINCIPALNAME()
RETURN Table2[LOCATION] = LOOKUPVALUE(Table1[LOCATION],Table1[EMAIL],_UPN)

jdbuchanan71_1-1696801807553.png

 

USERPRINCIPALNAME() gets the account of the person that is logged in.  The model uses that to get the region that user can see and apply the filter to Table2.

jdbuchanan71_0-1696801744380.png

I have attached my sample file for you to look at.

@jdbuchanan71 
Thank you for the reply 🙂
And here we go on the merrygoround of DAX lol
What i forgot to show in my example due to brain haze is that I need to granulate the data return further on Watch.
My example data didn't show that (Location can have 4-6 watches)


If i add an email address for the another user and check Employees
This also only shows the individual data of who is logged in not all the people at that location.


My real expression

VAR _UPN = USERPRINCIPALNAME()
RETURN
SA[location]
=
LOOKUPVALUE(
WMFSED[JOB_LOCATION],
WMFSED[WORK_EMAIL]
,_UPN)

 


WMFSED = Table 1
SA =Table2

 



Again I do appreciate the help on this
Gary

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors