Add alternating data in new column

[sipati]

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".

[amitchandak]

You can do it using earlier or rank 

check

https://community.powerbi.com/t5/Desktop/Count-previous-occurrences-of-a-name-in-each-row/td-p/35264...

https://community.powerbi.com/t5/Desktop/Find-the-date-of-the-previous-occurrence-of-the-id-and-past...

[sipati]

Sorry, I am new. If possible, can you share a proper DAX or M query? Thanks.

[v-piga-msft · Accepted solution]

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

[amitchandak]

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