Forum Discussion
Create a Calculated column where we can flag the task_id whenever there is a new date
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!
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)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!
4 Replies
- amitchandak
Super User
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)amitchandak Thanks a lot Amit. It did work for me.
- Jihwan_Kim
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!
Jihwan_Kim Thanks for your response, It did work for me!