Getting data from an unrelated table

[Danfountain]

Hi

These are the tables i current have

Users
UserList	Exchange Rate
Dom\ABC123	0.123
Dom\zzz112	0.789
Dom\SDF312	1.2

UserBU
User	BU
Dom\ABC123	1
Dom\ABC123	2
Dom\ABC123	3
Dom\zzz112	1
Dom\SDF312	1
Dom\SDF312	3

Sales
OrderNum	BU	Total Value
1	2	100
2	3	500
3	1	100
4	1	200
5	1	300
5	3	600
6	3	1000

So the user table has row level security to bring back the details of the user.  This then applies a relationship filter to the User BU table to find the BU`s a user can see.  Then from there it applies a relationship based on BU`s to show the sales within the BU`s that a user can see.  Hope that makes sense.  It seems to work.

Now my problem is i have exchange rate in the user table.  I want to use this to convert the each total value into the users exchange rate.

It doesnt seem to matter what i have tried it never seems to bring back the value filtered by the RLS in this table.

To begin with i am just using this to create a column

Weighted_LocalCurrency = min(User[exchangerate])

But the value isnt the same that is in the RLS filtered table for the user.  I just cannot work it out 🙂

Any ideas?

Dan