Forum Discussion
RELATED function and relationships between tables
Hi Anonymous ,
Do you want to create a date column in Drop table based on Date column in ShipDetails table?
If it is yes, please refer the following formula.
Create a calculate column in Drop table,
Column = CALCULATE(MAX(ShipDetails[Date]),FILTER(ShipDetails,ShipDetails[ID]=Drops[ID]))If it doesn't meet your requirement, could you please show the exact expected result based on the table that we have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
The cross filter directions both are single, the arrow from Drops table to Distance table is not circulating.
So we need to change the relationship between ShipDetails table and Distance table to Both direction.
And then create a measure and the result like this,
Measure = CALCULATE(SUM(Distance[Distance])) / CALCULATE(COUNTROWS('ShipDetails'))
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply？
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you v-zhenbw-msft !
Your suggestion works. However, in the case where the type of the column is text and not numbers as the Distance[DISTANCE] is, it won't work.
Best regards,
Marion
Hi Anonymous ,
SUM is an aggregate function. If you want to aggregate the Distance[DISTANCE], you need to change the type to whole number.
Or if you want to get the count of the Distance[DISTANCE], you can use COUNT function or DISTINCTCOUNT function.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry v-zhenbw-msft, this isn't what I meant, I wasn't clear enough.
I am creating a new column in the Drops table (the left table on the image I put in the first message) and for each DROP_ID, I need to access the corresponding date (the date of the drop). There is a column DATE in the ShipDetails table (the middle table on the image) but for every DROP_ID, there are multiple occurrences of the same date (there are several rows with the same DROP_ID, each having the same date).
How can I reference this date as a single value to use it in a calculated column in the Drops table? (I want to have the corresponding date for each DROP_ID)Was it clearer?
Does anyone have any idea? I really need your help.
Thank you very much!