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
Highlighted
Super User IV
Super User IV

Re: Circular dependency on calculated table with manager and decendants

@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
Helper III
Helper III

Re: Circular dependency on calculated table with manager and decendants

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
Super User II
Super User II

Re: Circular dependency on calculated table with manager and decendants

@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
Helper III
Helper III

Re: Circular dependency on calculated table with manager and decendants

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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors