Circular dependency when working with primary keys

[marcel97]

Hello all,

I am currently trying to remove a circular dependency in Power BI and could not find any solution when doing some research.

I am managing a dashboard for our project management office. Recently we got access to a data base with time booking data. Find attached the simplified data:

I have one team table, in which I have a team hierarchy:

TEAM_ORDER	TEAM_ID	TEAM_PARENT_ID	IDENTIFIER
A	1		A
D	2	1	AD
W	3	2	ADW
B	4	2	ADB
S	5	3	ADWS
N	6	3	ADWN
Y	7	4	ADBY

The time bookings I source look as follows:

DEPARTMENT_NAME	HOURS_BOOKING	PERIOD
ADWS	50	2022/02
ADWN	40	2022/02
ADBY	70	2022/02

I have added new columns to the team table in Power BI:

TEAM_ORDER	TEAM_ID	TEAM_PARENT_ID	IDENTIFIER	PATH	L1	L2	L3	L4
A	1		A	1	A
D	2	1	AD	1|2	A	AD
W	3	2	ADW	1|2|3	A	AD	ADW
B	4	2	ADB	1|2|4	A	AD	ADB
S	5	3	ADWS	1|2|3|5	A	AD	ADW	ADWS
N	6	3	ADWN	1|2|3|6	A	AD	ADW	ADWN
Y	7	4	ADBY	1|2|4|7	A	AD	ADB	ADBY

For PATH I have used the PATH function and for L1-L4 I have used a LOOKUPVALUE function, that returns TEAM_ORDER for the 1st, 2nd... respective PATH_ITEM {e.g. L1 =

LOOKUPVALUE(Team[TEAM_ORDER], Team[TEAM_ID], VALUE(PATHITEM(Team[PATH],1))) }

In the time bookings table I have added a column where I source the TEAM_ID:

DEPARTMENT_NAME	HOURS_BOOKING	PERIOD	TEAM_ID
ADWS	50	01. Feb 22	5
ADWN	40	01. Feb 22	6
ADBY	70	01. Feb 22	7

TEAM_ID = CALCULATE(FIRSTNONBLANK(Team[TEAM_ID],1), FILTER(ALL(Team), Bookings[DEPARTMENT_NAME]=Team[IDENTIFIER]))

Now when I try to create a relationship between the TEAM_ID columns I get the following error message that a circular dependency was created:

I know that it must originate from the L1-L4 calculations as I did not experience this issue without them, however, I need these columns for further processing.

Any way to work around this?

Any help is appreciated!

Thanks and kind regards

Marcel

[ValtteriN · Accepted solution]

Hi,

I recommend cheking this article by SQLBI. They cover the reasons for these kinds of errors quite well: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

[marcel97]

Hey @ValtteriN !

At first I thought the solutions provided in this articel did not help - turns out I had a syntax error in one of my columns.

Thanks a lot! Replacing ALL with ALLNOBLANKROW within the CALCULATE function did help indeed.

Kind regards

Marcel