RLS/Dynamic Filter Help needed

[gazzo1967]

HI All
Sample data
Table One (Employee TEST data)

NAME	EMAIL	LOCATION	WATCH	ROLE
Andy Jones	Andy.Jones@wm.net	A	red	Crew
Paul Smith	Paul.Smith@wm.net	B	green	Watch
Dave Brown	Dave.Brown@wm.net	C	white	Crew
Rich Davis	Rich.Davis@wm.net	D	blue	Crew

Table Two(test Data)

NAME	EMAIL	LOCATION	WATCH	ROLE	assessment
Andy Jones	Andy.Jones@wm.net	A	red	Crew	90
Paul Smith	Paul.Smith@wm.net	B	green	Watch	79
Dave Brown	Dave.Brown@wm.net	C	white	Crew	89
Rich Davis	Rich.Davis@wm.net	D	blue	Crew	76
Steve Blue	Steve.Blue@wm.net	D	blue	FF	80
Mike New	Mike.New@wm.net	B	green	FF	78
Pete Green	Pete.Green@wm.net	B	green	FF	76
Mike White	Mike.White@wm.net	A	red	FF	69

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)

NAME	EMAIL	LOCATION	WATCH	ROLE	assessment
Paul Smith	Paul.Smith@wm.net	B	green	Watch	79
Mike New	Mike.New@wm.net	B	green	FF	78
Pete Green	Pete.Green@wm.net	B	green	FF	76

If Rich Davis  logs in  they will see

Table Two(test Data)

NAME	EMAIL	LOCATION	WATCH	ROLE	assessment
Rich Davis	Rich.Davis@wm.net	D	blue	Crew	76
Steve Blue	Steve.Blue@wm.net	D	blue	FF	80

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 🙂

[jdbuchanan71 · Accepted solution]

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.

[gazzo1967]

@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 😁)

[jdbuchanan71]

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.

[gazzo1967]

@jdbuchanan71 
Again thank you for your support 
🙂
Gary

[jdbuchanan71]

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:

And this is what Pete.Green looks like:

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

[gazzo1967]

@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

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]

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.

[gazzo1967]

@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_name	email_address	location	job_watch	role_basic	assessment
Mike New	Mike.New@wm.net	B	green	FF	78
Pete Green	Pete.Green@wm.net	B	green	FF	76

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

full_name	email_address	location	job_watch	role_basic	assessment
Andy Jones	Andy.Jones@wm.net	A	red	Crew	90
Mike White	Mike.White@wm.net	A	red	FF	69

SA Table

full_name	email_address	location	job_watch	role_basic	assessment
Andy Jones	Andy.Jones@wm.net	A	red	Crew	90
Paul Smith	Paul.Smith@wm.net	B	white	Watch	79
Dave Brown	Dave.Brown@wm.net	C	white	Crew	89
Rich Davis	Rich.Davis@wm.net	D	blue	Crew	76
Steve Blue	Steve.Blue@wm.net	D	blue	FF	80
Mike New	Mike.New@wm.net	B	green	FF	78
Pete Green	Pete.Green@wm.net	B	green	FF	76
Mike White	Mike.White@wm.net	A	red	FF	69
Peter Long	Peter.Long@wm.net	B	blue	FF	69
Mitchell Bow	Mitchell.Bow@wm.net	B	red	FF	69

WMFSED table

NAME	WORK_EMAIL	JOB_LOCATION	JOB_WATCH	Role_Basic
Andy Jones	Andy.Jones@wm.net	A	red	Crew
Paul Smith	Paul.Smith@wm.net	B	white	Watch
Dave Brown	Dave.Brown@wm.net	C	white	Crew
Rich Davis	Rich.Davis@wm.net	D	blue	Crew
Steve Blue	Steve.Blue@wm.net	D	blue	FF
Mike New	Mike.New@wm.net	B	green	FF
Pete Green	Pete.Green@wm.net	B	green	FF
Mike White	Mike.White@wm.net	A	red	FF
Peter Long	Peter.Long@wm.net	B	blue	FF
Mitchell Bow	Mitchell.Bow@wm.net	B	red	FF

[gazzo1967]

@jdbuchanan71 
Apparently my post need moderator approval 😞

[gazzo1967]

@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_name	email_address	location	job_watch	role_basic	assessment
Mike New	Mike.New@wm.net	B	green	FF	78
Pete Green	Pete.Green@wm.net	B	green	FF	76

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

full_name	email_address	location	job_watch	role_basic	assessment
Andy Jones	Andy.Jones@wm.net	A	red	Crew	90
Mike White	Mike.White@wm.net	A	red	FF	69

SA Table

full_name	email_address	location	job_watch	role_basic	assessment
Andy Jones	Andy.Jones@wm.net	A	red	Crew	90
Paul Smith	Paul.Smith@wm.net	B	white	Watch	79
Dave Brown	Dave.Brown@wm.net	C	white	Crew	89
Rich Davis	Rich.Davis@wm.net	D	blue	Crew	76
Steve Blue	Steve.Blue@wm.net	D	blue	FF	80
Mike New	Mike.New@wm.net	B	green	FF	78
Pete Green	Pete.Green@wm.net	B	green	FF	76
Mike White	Mike.White@wm.net	A	red	FF	69
Peter Long	Peter.Long@wm.net	B	blue	FF	69
Mitchell Bow	Mitchell.Bow@wm.net	B	red	FF	69

WMFSED table

NAME	WORK_EMAIL	JOB_LOCATION	JOB_WATCH	Role_Basic
Andy Jones	Andy.Jones@wm.net	A	red	Crew
Paul Smith	Paul.Smith@wm.net	B	white	Watch
Dave Brown	Dave.Brown@wm.net	C	white	Crew
Rich Davis	Rich.Davis@wm.net	D	blue	Crew
Steve Blue	Steve.Blue@wm.net	D	blue	FF
Mike New	Mike.New@wm.net	B	green	FF
Pete Green	Pete.Green@wm.net	B	green	FF
Mike White	Mike.White@wm.net	A	red	FF
Peter Long	Peter.Long@wm.net	B	blue	FF
Mitchell Bow	Mitchell.Bow@wm.net	B	red	FF

[jdbuchanan71]

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]

@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]

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)

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.

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

[gazzo1967]

@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