Forum Discussion
Return values from conditional column within each group
Hello everybody,
I would like to create new column [Name Revised], which updates the correct name for each ID. For example, if there are in the same ID, the correct name is the name that have the earliest date.
| Name | Date | Value | ID | Name Revised |
| A | 12/1/2021 | 10 | 123456 | A |
| B | 12/3/2021 | -10 | 123456 | A |
| C | 12/1/2021 | 20 | 123789 | C |
| D | 12/5/2021 | -20 | 123789 | C |
I have tried many approaches but they still doesnt work (TOPN, FIRSTNONBLANK, creating summarize table then LOOKUPVALUES, ..).
Should you have any idea to solve this issue, please share with me.
Many Thanks.
Quynh Tran.
Name Revised CC =
VAR _currentID = Data[ID]
VAR _mindate =
CALCULATE ( MIN ( Data[Date] ), FILTER ( Data, Data[ID] = _currentID ) )
RETURN
CALCULATE (
DISTINCT ( Data[Name] ),
FILTER ( Data, Data[ID] = _currentID && Data[Date] = _mindate )
)
2 Replies
- Jihwan_Kim
Super User
Name Revised CC =
VAR _currentID = Data[ID]
VAR _mindate =
CALCULATE ( MIN ( Data[Date] ), FILTER ( Data, Data[ID] = _currentID ) )
RETURN
CALCULATE (
DISTINCT ( Data[Name] ),
FILTER ( Data, Data[ID] = _currentID && Data[Date] = _mindate )
)Thanks Jihwan,
It works perfectly.
