New column that repeats value into each row based on value in existing rows

[Anonymous]

Hey all, 

I think that this has an easy solution, but I'm a little rusty with Power BI.

I've been able to isolate dates that I need based on other parameters, which allowed me to establish a CC and OA date per SOW.  (SOW, CC and OA are good.)

Now I need to calculate the columns for New CC and New OA.  These are just a repeat of the values from CC and OA, but the value fills every row, based on SOW. The general logic is 'for each row in SOW X, look for a date in column CC and populate to all rows in New CC'. Repeat for OA/New OA.

Can anybody point me in the direction of which function I should use? I understand that this is probably also solvable with PowerQuery but for reasons I want to solve it in DAX.

SOW	CC	OA	New CC	New OA
1111			1/30/2021	2/3/2021
1111			1/30/2021	2/3/2021
1111	1/30/2021		1/30/2021	2/3/2021
1111			1/30/2021	2/3/2021
1111		2/3/2021	1/30/2021	2/3/2021
2222			3/6/2021	3/10/2021
2222			3/6/2021	3/10/2021
2222	3/6/2021		3/6/2021	3/10/2021
2222		3/10/2021	3/6/2021	3/10/2021
3333			2/18/2021	3/20/2021
3333		3/20/2021	2/18/2021	3/20/2021
3333			2/18/2021	3/20/2021
3333	2/18/2021		2/18/2021	3/20/2021
3333			2/18/2021	3/20/2021
4444			6/1/2021	6/15/2021
4444			6/1/2021	6/15/2021
4444			6/1/2021	6/15/2021
4444	6/1/2021		6/1/2021	6/15/2021
4444		6/15/2021	6/1/2021	6/15/2021
4444			6/1/2021	6/15/2021

[AlB · Accepted solution]

Hi @Anonymous 

Create calculated columns:

New CC =
CALCULATE ( MAX ( Table1[CC] ), ALLEXCEPT ( Table1, Table1[SOW] ) )

New OA =
CALCULATE ( MAX ( Table1[OA] ), ALLEXCEPT ( Table1, Table1[SOW] ) )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thanks millions!  This is exactly it!