Circular dependency on calculated table with manager and decendants

[clausm73]

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'.

dim_managerhierarchy = DATATABLE(
    "WORKERKEY", INTEGER,
    "PARENTWORKERKEY", INTEGER,
    "NAME", STRING,
    "VALIDFROM", DATETIME,
    "VALIDTO", DATETIME,
    "MANAGERHIERARCHYKEY", INTEGER,
    "PARENTMANAGERHIERARCHYKEY", INTEGER,
    {
        {1, BLANK(), "Boss", "2020-01-01", "2020-12-31", 1, BLANK() },
        {2, 1, "Sales Manager", "2020-01-01", "2020-12-31", 2, 1 },
        {3, 2, "Employee", "2020-01-01", "2020-06-30", 3, 2 },
        {3, 1, "Employee (Promoted)", "2020-07-01", "2020-12-31", 4, 1 }        
    }
)

Here is formula used to create the bridge table

=
/*
Description:
1. For each worker find historic hierarchy keys in the manager hierarchy.
2. For each hierarchy key - find any descendant historic hierarchy keys using PATHCONTAINS( PATH( <Historic Key>, <Manager Historic Key> ), <Historic Key> ) ).
3. DONE - we have build a list to map all workers to the historic versions of their descendant workers.
*/
GENERATE (
    SELECTCOLUMNS (
        ALLNOBLANKROW ( dim_managerhierarchy ),
        "WORKERKEY", dim_managerhierarchy[WORKERKEY],
        "WORKERHIERARCHYKEY", dim_managerhierarchy[MANAGERHIERARCHYKEY]
    ),
    CALCULATETABLE (
        dim_managerhierarchy,
        FILTER (
            ALLNOBLANKROW ( dim_managerhierarchy ),
            PATHCONTAINS ( dim_managerhierarchy[PATH_UNIQUE], [WORKERHIERARCHYKEY] )
        )
    )
)

 
I hope someone can help me to contruct this bridge table in DAX - so that I won't get a dependency error.

[amitchandak]

@clausm73 , usually tables created with Summarize and distinct do not give this issue.  But there are few cases they can.

Try to use distinct on top of your table script and check if that can help. Hope it is not a big data table

[clausm73]

I did not manage to solve the problem blending in SUMMARIZE in the DAX statement.

However - I did get a slight performance improvement using this expression:

=
/*
Description:
1. For each worker find historic hierarchy keys in the manager hierarchy.
2. For each hierarchy key - find any descendant historic hierarchy keys using PATHCONTAINS( PATH( <Historic Key>, <Manager Historic Key> ), <Historic Key> ) ).
3. DONE - we have build a list to map all workers to the historic versions of their descendant workers.
*/
GENERATE (
    SELECTCOLUMNS (
        SUMMARIZE (
            ALLNOBLANKROW ( dim_managerhierarchy ),
            dim_managerhierarchy[WORKERKEY],
            dim_managerhierarchy[MANAGERHIERARCHYKEY]
        ),
        "WORKERKEY", dim_managerhierarchy[WORKERKEY],
        "WORKERHIERARCHYKEY", dim_managerhierarchy[MANAGERHIERARCHYKEY]
    ),
    SUMMARIZE (
        FILTER (
            SUMMARIZE (
                ALLNOBLANKROW ( dim_managerhierarchy ),
                dim_managerhierarchy[MANAGERHIERARCHYKEY],
                "PATH", MIN ( dim_managerhierarchy[PATH_UNIQUE] )
            ),
            PATHCONTAINS ( [PATH], [WORKERHIERARCHYKEY] )
        ),
        dim_managerhierarchy[MANAGERHIERARCHYKEY]
    )
)

[PaulDBrown]

@clausm73 

Have you tried creating the dimension table in Power Query? Reference your fact table, remove unnecessary columns, remove duplicate rows, rename table, load and create relationship.

[clausm73]

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". ☹