RELATED function and relationships between tables

[Anonymous]

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 :

SUMX(RELATEDTABLE(PeripheralTable),PeripheralTable[COL])/COUNTROWS(RELATEDTABLE(PeripheralTable))

Is there any function that returns the first row only for instance or anything else that I could use to fix this problem?

Please help me.

Thank you very much !

Marion

Here is a hardcoded example :

[v-zhenbw-msft]

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.

[Anonymous]

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

[v-zhenbw-msft]

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]

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!

[v-zhenbw-msft · Accepted solution]

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.

[Anonymous]

Yes this is it, and it works! Thank you very much @v-zhenbw-msft .

Kind regards,

Marion

[amitchandak]

@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.

[Anonymous]

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 ?