Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
@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
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
@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")
Hi @amitchandak
var _max= maxx(filter(Table, [Building] =earlier([Building]) && [Date] =_max),[Status])
it's erroring with the _max "Cannot find name _max"
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.