cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AUaero
Resolver I
Resolver I

Calculated table generates a circular dependency when joined

Hi,

I've built a calculated table to rank salespeople based on several metrics over the last 365 days.  Here's the code:

 

Magnolia Club ISR = 
VAR tblData = 
CALCULATETABLE(
	ADDCOLUMNS(
		SELECTCOLUMNS(
			FILTER(
				ALLNOBLANKROW(ISR),
				ISR[ISRJobTitle] in  {"Inside Sales ONS", "Inside Sales Trainee ONS"}
			),
			"ISRName", ISR[ISRName],
			"ISRNumber", ISR[ISRNumber]
		),
		"BilledGrossMargin", 'Sales Order'[$ Billed WAC GM],
		"BilledTons", 'Sales Order'[# Billed Tons],
		"BuyingPRONTOAccounts", Customer[# Buying PRONTO Customers],
		"BilledProcessedGM", 'Sales Order'[$ Billed Processed GM],
		"BilledSpecialtyGM", 'Sales Order'[$ Billed Specialty GM],
		"AdminQDR", 'Sales Order'[% QDR - Admin]
	),
	FILTER(
		ALLNOBLANKROW('Calendar'[Trailing365DaysFlag]),
		'Calendar'[Trailing365DaysFlag] = "Trailing 365 Days"
	)
)

VAR tblRank = 
ADDCOLUMNS(
	tblData,
	"BilledGrossMarginRank", RANKX(tblData, [BilledGrossMargin], , DESC, Skip),
	"BilledTonsRank", RANKX(tblData, [BilledTons], , DESC, Skip),
	"BuyingPRONTOAccountsRank", RANKX(tblData, [BuyingPRONTOAccounts], , DESC, Skip),
	"BilledProcessedGMRank", RANKX(tblData, [BilledProcessedGM], , DESC, Skip),
	"BilledSpecialtyGMRank", RANKX(tblData, [BilledSpecialtyGM], , DESC, Skip),
	"AdminQDRRank", RANKX(tblData, [AdminQDR], , DESC, Skip)
)

VAR tblRankSum = 
ADDCOLUMNS(
	tblRank,
	"RankSum", [BilledGrossMarginRank] + [BilledTonsRank] + [BuyingPRONTOAccountsRank] + [BilledProcessedGMRank] + [BilledSpecialtyGMRank] + [AdminQDRRank]
)

RETURN 
ADDCOLUMNS(
	tblRankSum,
	"OverallRank", RANKX(tblRankSum, [RankSum], , ASC, Skip)
)

 

The problem arises when I try to join this calculated table back to my salesperson table (ISR) on the ISRNumber field:

AUaero_0-1638217717657.png

However, when I try to do this I get a circular dependency.  I've tried eliminating the blank row as a factor in the join to eliminate the dependency per the SQLBI blog (Understanding circular dependencies in DAX - SQLBI) but I'm still running into problems.

Any ideas?

Thanks!

 

1 ACCEPTED SOLUTION

Update 2 - the fix was easy enough, even if figuring out the problem with circular dependency isn't always evident.  By eliminating the blank row on the Customer table I was able to successfully build the realtionship in my data model.  My new measure is:

# Buying PRONTO Customers = 
SUMX(
    ALLNOBLANKROW(Customer),
    SWITCH(
        TRUE(),
        Customer[# E1 Buying PRONTO Customers] > 0, 1,
        Customer[# CMA Buying PRONTO Customers] > 0, 1
    )
)

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi @AUaero ,

 

You can try restrict the list of columns that the calculated column depends on, by using ALLEXCEPT or REMOVEFILTERS and keeping only the table’s primary key. If the table has no primary key, then using CALCULATE in a calculated column is dangerous.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AUaero
Resolver I
Resolver I

Update - 
Further troubleshooting has narrowed it down to a particular measure that is causing the problem.  The column "BuyingPRONTOAccounts" on variable "tblData" is generated from a measure on the "Customer" table named "# Buying PRONTO Customers".
# Buying PRONTO Customers is calculated from two other measures, "# E1 Buying PRONTO Customers" and "# CMA Buying PRONTO Customers".

# Buying PRONTO Customers = 
SUMX(
    Customer,
    SWITCH(
        TRUE(),
        Customer[# E1 Buying PRONTO Customers] > 0, 1,
        Customer[# CMA Buying PRONTO Customers] > 0, 1
    )
)

# E1 Buying PRONTO Customers = 
CALCULATE(
    Customer[# E1 Buying Customers],
    'E1 Sales'[DocType] = "SH"
)

# CMA Buying PRONTO Customers = 
CALCULATE(
    Customer[# CMA Buying Customers],
    Channel[Channel] = "Pronto"
)

If I modify the BuyingPRONTOAccounts column in tblData to reflect either # E1 Buying PRONTO Customers or # CMA Buying PRONTO Customers, I don't get a circular dependency on the join between ISR and Customer.

How can I modify # Buying PRONTO Accounts to eliminate the circular dependency?
Thanks!



Update 2 - the fix was easy enough, even if figuring out the problem with circular dependency isn't always evident.  By eliminating the blank row on the Customer table I was able to successfully build the realtionship in my data model.  My new measure is:

# Buying PRONTO Customers = 
SUMX(
    ALLNOBLANKROW(Customer),
    SWITCH(
        TRUE(),
        Customer[# E1 Buying PRONTO Customers] > 0, 1,
        Customer[# CMA Buying PRONTO Customers] > 0, 1
    )
)

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors
Top Kudoed Authors