Circular dependency on calculated table with manager and decendants
I'm trying to calculate a table - a bridge table - with all descendants of a manager at any point-in-time from a slowly changing dimension with a parent-child hierarchy. This mapping table is to be used for Row-Level Security so its going to be joined back to the table its calculated from.
But I keep getting a circular dependency error when I try to create a relationship on column [MANAGERHIERARCHYKEY]:
Here is a script to create a sample of my slowly changing dimension table 'dim_managerhierarchy'.
Well, as it is a Parent-Child hierarchy for employees (in a Slowly Chagning Dimension) - most of the dimension attributes are created as calculated columns in the Tabular Model using DAX. And I'm not about to rewrite all that logic to Power Query-statements.
So my "workaround" has unfortunately been to NOT make the relationships, but use crossfiltering in my filter expression for Row-Level Security.
This works, but will not make it possible to do "slicers" based filters on WORKERKEY in the created bridge table making it possible to do "impersonation" of managers in reports where a user has been assigned "rows" in a database to allow for "impersonation". ☹