Hello -
I am working in a Embedded BI/ISV environment, we use a single PBI instance/dataset with RLS to determine which information is shown to which client through our application.
We are in financial tech, and want to show Foreign Exchange (FX) rates to clients, which are not client specific, and so do not have any RLS applied. However, clients can also enter their own "custom" rates, which are client specific, and should only be shown to the clients who own that particular rate. The "custom" rate should override the rate on the generic table.
Our original thought was to do a custom column (dax) on the Generic Rates Table, that would look at the Custom Rates Table and if a rate was present it would use that, otherwise it would take the rate from the Generic Table (i.e. create new calculated column and the formula would be: for 01/01/2022, EUR-USD, if rate on Custom Table, Then Use Custom Rate, if not use generic rate for that date/rate combination).
However that approach doesnt work because columns in dax are calculated in memory prior to RLS kicking in when the report is rendered. So, we are not sure where to go, any help would be appreciated, or ideas that we could start running down! Thank you community in advance!
Generic Rates Table
| Date | Currency | Rate |
| 01/01/2022 | EUR-USD | 1.12 |
| 01/02/20222 | EUR-USD | 1.13 |
| 01/01/2022 | GBP-USD | 1.5 |
Custom Rates Table
| Date | Currency | Rate | Client_Guid |
| 01/01/2022 | EUR-USD | 1.14 | fc4d1222-fdb5-496e-89a2-3fb06ef51f86 |
| 01/02/20222 | EUR-USD | 1.15 | fc4d1222-fdb5-496e-89a2-3fb06ef51f86 |
| 01/01/2022 | GBP-USD | 1.6 | 0000853b-aa7e-42cb-9dba-dfded536d489 |
