cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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]:

PBIDesktop_SyPVWqVekO.png


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.

4 REPLIES 4
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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]
    )
)
Highlighted

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors