AMBUIGITY! - 2 User Dimensions, 2 Fact Tables - Select Filters

[SHS]

Hi All,

After several attempts, I have come to the conclusion that I need support 😊

Data Model
We have a KPI Report which contains 2 fact tables: 1 for KPI Targets and 1 for Performance. Additionally we have 2 user dimensions: 1 for Sales Manager and 1 for Sales Employees.

Why 2 User Dimensions
The reason we don’t have a combined is due to Sales Managers requiring a different relationship to the Fact Performance-table. For KPI Targets it is no issue as all the user targets he/she should be able to see is registered under his/hers sales organization. However, for the Performance, due to special cases and general data errors, we experience that accounts initially handled by their sales teams are allocated towards a Sales Employee outside of their team. Yet, the Sales Manager should still be able to see the performance related to this account as it is within his sales area, but should of course not be able to see the users KPI targets as he/she is not part of their team.

The Issue Reported by Users
We have a front-page containing 3 default filters and these are the primary issue point for the users right now. We have a Region and Sales org filter from Sales Manager Dimension and Employee filter from Sales Employee Dimension. Due to the relationship being 1-way for both dimensions, a sales manager will see the full list of sales employees rather than only the ones within his time, and vice versa, a Sales Employee will see all Regions and Sales Organizations.

Solutions Considered
I have considered a bridge table, but due to the different granularity required, I would require me to use USERELATIONSHIP in RLS, which I find contradicting information to whether it is possible at all (and until now, the tests have not succeeded).

I have tried to merge the filter-columns into either fact table (know this is definitely not the right procedure), but then the filters only work that specific fact table.

The last solution I had would be to create the combined user dimension, creating 2 relationship towards Performance and then use the USERELATIONSHIP on all KPI Measures in the report. However, I find it hard to believe that this should be the only and best solution.

So before doing this, I was wondering if anyone has experienced this case and solved it in a simpler way?