Forum Discussion
RELATED function and relationships between tables
Hi !
I'm beginning on Power BI and I have a problem that keeps arising.
I have a central table and other tables that gravitate around it, with many to 1 relationships from the central table to the others. Therefore, when I want to access data from a peripheral table to another, with the RELATED function, I can't because it can't found a unique value, whereas in fact all the values are leading to the same one (as in the example below).
The only solution I found is this, but it's not very clean and more importantly it only works for numerical values :
Please help me.
Thank you very much !
Marion
Here is a hardcoded example :
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.
8 Replies
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.
Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.Sure amitchandak !
If I take the tables I showed in the example above, it would occur if I tried to calculate a column in the left table (named Drops, with the IDs) needing the distance. The piece of information about the distance is in the right table (Distances). In order to access it, I want to use a function like the RELATED function but it won't work since it'll have to go through the mid table, and there will be to many paths leading to the value I want, so it won't recognize it as a single value.
I don't know if that was clear, did you understand ?
