Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) )
Solved! Go to 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.
Hi @aukev
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]))
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.
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.
Hi,
It would be much better with you create in your model a relationship between the two tables.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |