Create a Calculated column where we can flag the task_id whenever there is a new date

[Anonymous]

Hello Everyone,

I am trying to create a calculated column where the column flags the task_id with 1 if there is a new date or else 0 if the date is repeated. Given below is the Table of what I am trying to build. 

Date                    Task_Id            Calculated_Column

01/01/2020          100                             1

01/01/2020          100                             0

01/01/2020          100                             0

25/02/2020          100                             1

25/02/2020          100                             0

25/02/2020          100                             0

15/03/2020          200                             1

15/03/2020          200                             0

15/03/2020          200                             0

08/04/2020          200                             1

08/04/2020          200                             0

Please help!

Thanks in advance!

[amitchandak · Accepted solution]

@Anonymous , You have to add an index in the power query first and then add a new column in DAX

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

new column =
if([Index] = minx(filter(Table, [Task_Id] = earlier([Task_Id]) && [date] =earlier([Date])),[Index]) ,1,0)

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please correct me if I am wrong.

Because there are some duplicated rows in the table, I created one additional column (Index) in order to differentiate each row and compare one row with the previous row.

Then, I created the following Calculated Column.

flag task_id =
IF (
newtable[date]
= CALCULATE (
MAX ( newtable[date] ),
FILTER ( newtable, EARLIER ( newtable[Index] ) > newtable[Index] )
),
0,
1
)

Please check if it is suitable for your task.

Did I answer your question? Mark my post as a solution, this will help others!

[Anonymous]

@Jihwan_Kim Thanks for your response, It did work for me!

[amitchandak · Accepted solution]

@Anonymous , You have to add an index in the power query first and then add a new column in DAX

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

new column =
if([Index] = minx(filter(Table, [Task_Id] = earlier([Task_Id]) && [date] =earlier([Date])),[Index]) ,1,0)

[Anonymous]

@amitchandak Thanks a lot Amit. It did work for me.