Find a specific row of a one-to-many relationship and return a value

[Shruikan]

Hello everyone

Very new in Power BI (e tbh reports).

I have two tables in a one-to-many relationship where the parent's "registration ID" is the key to the related child records. Each row in the parent table relates to many rows in the child table.

I'm trying to create a report in which I need to extract a specific child record based on a column and then return another column from the same row.

Sample tables:

Table 1 - Parent Table

Registration ID	Number
1234	Already
5678	Tina

Table 2 - children's table

Parent link	Number	Value
1234	date_1	12/03/2010
1234	date_2	05/05/2012
1234	text_1	Test text here
1234	text_2	Some how things test here
5678	date_1	29/08/2009
5678	date_2	11/12/2020
5678	text_1	More test placeholder material
5678	text_2	testing 123

What I need in the report:

For each record in Table 1 I need to add a column "Date 1" and "Date 2" and fill it with the "value" field in Table 2 where "Date 1" is the secondary record related to Name "date_1" and "Date 2" is the secondary record related to Name "date_2".

Example of output:

Number	Date 1	Date 2
Already	12/03/2010	05/05/2012
Tina	29/08/2009	11/12/2020

Any guidance on how I can do this would be greatly appreciated. Thank you!

[FrankAT · Accepted solution]

Hi,

see figure:

Regards FrankAT

[Shruikan]

You

Are

Amazing

Thank you so much, and thanks for the quick response!

Just for the record I had to replace ";" with "," for it to work, but other than that this is exactly what I needed. You're the best.