Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
clausm73
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
amitchandak
Super User
Super User

@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

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

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






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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.