Calculated table generates a circular dependency when joined

[AUaero]

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:

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!

[v-yangliu-msft]

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]

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!

[AUaero · 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
    )
)