Forum Discussion
Solved
Calculated column or measure? Data from specific row for all rows matching value in another column.
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?
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.
3 Replies
- Greg_Deckler
Community Champion
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.
Great, thank you!
That's exactly what I was looking for.
I really appreaciate your help.
- Greg_Deckler
Happy to help!
