New column that identifies two in a row or more

[Boycie92]

Hi,

I am looking for some advice.

I have a table that looks like this.

Building	Date	Task	Status
Building 1	Lock Doors	01/01/2020	Done
Building 1	Hover	01/02/2020	Not Done
Building 2	Empty bins	02/01/2020	Not Done

I want to add another column to it that will count reoccurring instances. So, if a task has not be done (Not Done) twice or more In a row I want it to say “reoccurring” else I want it to say “not reoccurring”.

Does anyone have any suggestions on how to get it into this format using either Power Query or DAX?

Thanks,

Boycie92

[Anonymous]

@Boycie92 

Can you please supply a representative set of data first of all and show what the data should look like after a column is added with the correct entries?

Thanks.

[amitchandak]

@Boycie92 , Try a new column like

new column =
var _cnt= countx(filter(Table, [Building] =earlier([Building]) && [Status] = "Not Done"),[Status])+0
return
if(_cnt >=2, "reoccurring" , "not reoccurring")

if needed you can add a similar condition after

[Building] =earlier([Building]) (to reduce scope of partition

[Boycie92]

@amitchandak 

Thanks for getting in touch and the help.

However it dosn't seem to be working as expected.

For example I would expect this to say "not reoccurring" as the last time the task was supposed to have been completed the Status was marked as "Done" any idea why?

Thanks,

Boycie92

[amitchandak]

@Boycie92 , check if this can work

new column =
var _cnt= maxx(filter(Table, [Building] =earlier([Building]) ),[Date])
var _max= maxx(filter(Table, [Building] =earlier([Building]) && [Date] =_max),[Status])
return
if([Status] = "not reoccurring", "reoccurring")

[Boycie92]

Hi @amitchandak 

var _max= maxx(filter(Table, [Building] =earlier([Building]) && [Date] =_max),[Status])

it's erroring with the _max "Cannot find name _max"

[v-kkf-msft · Accepted solution]

Hi @Boycie92 ,

Try the following formula:

Column = 
var count_ = 
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Building] = EARLIER('Table'[Building])
            && 'Table'[Task] = EARLIER('Table'[Task])
            && 'Table'[Status] = "Not Done"
        )
    )
return 
    IF(
        count_ >= 2,
        "reoccurring",
        "not reoccurring"
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.