Calculated column or measure? Data from specific row for all rows matching value in another column.

[JamesLeach]

Hello everyone!  

I've got a data set similar to this (without the 'UniqueId' column - that's what I'm trying to add).

Record	Data	Value	UniqueId
1	UniqueId	1001	1001
1	Thomas	Boise	1001
1	Sales	2915.35	1001
1	Range	-25%	1001
2	Cory	Jackson	1002
2	Vincent	Charleston	1002
2	Region	PNW	1002
2	UniqueId	1002	1002
3	UniqueId	1001	1001
3	Victor	Cheyenne	1001
3	Retired	$true	1001
4	UniqueId	1003	1003
4	Justin	Salt Lake City	1003
5	UniqueId	1001	1001
5	Brandon	Des Moines	1001
5	Peter	Sacramento	1001
5	Joel	Baton Rouge	1001
5	Range	44%	1001
6	Jacob	Hartford	1002
6	UniqueId	1002	1002
6	Notes	Discontinued	1002
7	Manager	cc26-4f08	1004
7	Derek	Atlanta	1004
7	UniqueId	1004	1004

Each 'Record' will have a 'UniqueId' which I would like to add to each row.  I've done similar things with DAX, using CALCULATE and FILTER, but am having difficulity figuring this one out.   

Does anyone have any suggestions? 

[Greg_Deckler · Accepted solution]

As a calculated column, you could do this:

UniqueID = 
VAR __table = FILTER(ALL('Table2'),[Record]=EARLIER([Record]) && [Data]="UniqueId")
RETURN
MAXX(__table,[Value])

As a measure, it would be:

mUniqueID = 
VAR __record = MAX([Record])
VAR __table = FILTER(ALL('Table2'),[Record]=__record && [Data]="UniqueId")
RETURN
MAXX(__table,[Value])

Assuming you put it in a visualization with at least Record.

[JamesLeach]

Great, thank you!  

That's exactly what I was looking for.   

I really appreaciate your help.

[Greg_Deckler]

Happy to help!