Retrieving and spreading data from many side of a one-to-may relation

[Anonymous]

Hi,

I'm looking to get data from one table to another, but it's from the many-side of a many-to-one relation.

I have two tables.

Table 1 (1-side)

Table 2 (n-side)

While retrieving the data I'm seeking to calculate two columns in table 1.

1. The earliest value of the Timestamp with the Timestamp Code "Start"
2. The latest value of the Timestamp with the Timestamp Code "End"

Meanwhile I need the Timestamps to be present for all Machines used for each Prod No.

So I'm looking for Table 1 to look like shown below:

I appreciate any help! Thanks!

[SpartaBI · Accepted solution]

@Anonymous try these 2 calculated columns in table 1:

Earleist Start = 
CALCULATE(
	MIN('Table2'[Timestamp]),
	'Table2'[Timestamp Code] ="Start",
	ALLEXCEPT('Table1', 'Table1'[Prod No])
)

Latest End = 
CALCULATE(
	MAX('Table2'[Timestamp]),
	'Table2'[Timestamp Code] ="End",
	ALLEXCEPT('Table1','Table1'[Prod No])
)

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

Hi @SpartaBI ,

It works like a charm! Thank you!

[SpartaBI]

@Anonymous my pleasure 🙂
Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂