Forum Discussion
Solved
Add alternating data in new column
I have table like this:
| ID | Date |
| 1 | 12/01/2019 |
| 2 | 14/01/2019 |
| 2 | 15/01/2019 |
| 3 | 16/01/2019 |
| 3 | 17/01/2019 |
I want it to be like this:
| ID | Date | Status |
| 1 | 12/01/2019 | Unresolved |
| 2 | 14/01/2019 | Unresolved |
| 2 | 15/01/2019 | Resolved |
| 3 | 16/01/2019 | Unresolved |
| 3 | 17/01/2019 | Resolved |
So basically in the alternating format ,I want to add the third column with values: Unresolved, Resolved.
So if a ID occurs first time, it should be "Unresolved", if it is found again then "Resolved".
Hi sipati ,
Please try the calculated column below.
new column = VAR index = RANKX ( FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ), 'Table'[Date], , ASC ) RETURN IF ( index = 1, "Unsloved", "Resolved" )Here is the output.
Best Regards,
Cherry
4 Replies
Sorry, I am new. If possible, can you share a proper DAX or M query? Thanks.
try something like this
CALCULATE(MAX(Table[Date]),FILTER(Data,Table[ID]=EARLIER(Table[ID])&&Table[Date]<EARLIER(Table[Date])))
and use the output to build case statement
