Show first field of one-to-many relationship in a calculated column of the one-table

[AP_BI]

Hello together,

I have two tables connected via "Serialnumber" in a one to many relationship. Now I want to create a calculated column in the one-table for the first (text) "Reason" by date from the many-table. 

I tested out the RELATEDTABLE function but this results in an error. Can anyone help on this?

For example: There are 5 rows for this "Serialnumber" with possibily 5 different "Reason". I want to display the first "Reason" in the table on the "One"-side of the relationship. First means first date.

Thank you!!
Andreas

[negi007 · Accepted solution]

@AP_BI 

there are many ways to do it.

so what i have done, i have created a calc column in the second table where you have multiple reasons against same SI No and we have to find out the one which was the first recorded (min date)

we can create a below calc column this will sort the date and give the ranking within each reason

Rank_Order = RANKX(FILTER(Table2,Table2[SI NO]=EARLIER(Table2[SI NO])),Table2[Date],,ASC)

once you have the rank order colum, you can simply filter rank 1 and it will show the first reason agianst each SI No.

now from here, you can have multiple ways to show the reason

1. creating a new table using groupby 

Table3 = GROUPBY(FILTER(Table2,Table2[Rank_Order]=1),Table2[SI NO],Table2[Reason])

now you can use lookup function in the first table to pull reason from table3

below is the final output

i hope this can take you in the right direction. i am also attachting pbix file for help. thanks