I AM NOT GETTING SOLUTION HELP ?

[malhotra6675]

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?

[rohit1991 · Accepted solution]

Hi @malhotra6675 

The reason your formula doesn’t work is because you created a calculated column. Calculated columns are fixed at refresh time and don’t respect Row Level Security.

You need to use a measure instead:

User Exchange Rate =
SELECTEDVALUE(Users[ExchangeRate])

Then multiply your sales values by this rate:

Total=
SUMX ( Sales, Sales[Total Value] * [User Exchange Rate] )

This way the exchange rate will always come from the RLS-filtered Users table, and each user will see their own correct conversion.