Avoiding a circular dependency when having a calculated column that references the other table

[aukev]

I have these two tables. Now I've added a calculated column to the Order table (see dax below).

The idea is that this is a best estimate of which call belongs to which order. This works fine but now I want to setup a relation between the two. Based on the historyID in Calls and the new calculated column with the history id in the orders table. This creates a circular dependecy. I think I understand why, but I don't now how to avoid this or how to go around it. I need to setup the relation to calculate cost/revenue for calls. Any ideas on how to go about this?

Calls
callDate	Customer Phone	historyID
Order
dateKey	orderID	Phone	callID

callID = 
CALCULATE(
    MIN(callDataIncoming[historyID]),
    FILTER (
        CALCULATETABLE( VALUES (callDataIncoming)),
        DAY(callDataIncoming[callDate]) == DAY(magentoOrders[dateKey]) &&
        MONTH(callDataIncoming[callDate]) ==  MONTH(magentoOrders[dateKey]) &&
        YEAR(callDataIncoming[callDate]) == YEAR(magentoOrders[dateKey]) &&
        callDataIncoming[Customer Phone] == magentoOrders[Phone]
    )
)

[v-juanli-msft]

Hi @aukev 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @aukev 

You could create columns in "call" table

Column = CALCULATE(SUM('Order'[cost]),FILTER('Order','Order'[dateKey]=calls[callDate]&&'Order'[Phone]=calls[Customer Phone]))

Column 2 = CALCULATE(MIN(calls[historyID]),ALLEXCEPT(calls,calls[callDate],calls[Customer Phone]))

If it doesn't satisfy your requirement, please let me know.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mauriciosotero · Accepted solution]

Now I've undertand.

You have to create a CallID in the two table. For the table Calls, the CallID will be the primary key, and in the Order table the CallID will be the foreing key.

In the Calls table, create a CallID like:

CallID = CONCATENATE(callDate, Customer Phone)

and in the Order table:

CallID = CONCATENATE(dateKey, Phone)

After thar, you can create the relationship.

[aukev]

Hi, thanks. The problem is that there can be multiple calls on the same day and these are not necessarily connected with an order.

So creating a call id like that on the calls table will create multiple calls ids that are the same.

But then again maybe it's okay if these have the same id as multiple calls to the same customer probably mean that it's about the same order.

[mauriciosotero]

Hi,

It would be much better with you create in your model a relationship between the two tables.

[aukev]

The problem is with just a relationship I wouldn't know which call belongs to which order. I'd have to match these first by checking the date and call extention. Or am I missing something?