First blank date(S) of a group.

[Greg888]

I think I have found the ultimate 'impossible to achieve' DAX/Column measure problem.

Recently I posted regarding group of steps in a table, which eventually worked perfectly.

The scenario is this. I have a table with multiple document steps, what I want to do is capture the last step in the workflow.
However there are many times where a step in the workflow has been added but the step is not started or finished, just added for completness etc.,

So I need to find the first occurence of when the TaskStartDate and TaskEndDate are blank as this is then the current step.

I have tried so many things now but I am convinced this is not possible now as there is no way of knowing which the current step is based on the date field.

Any help on this massively aapreciated.

ScreenShot 1 is the document list (below)

ScreenShot 2 is the group by results and the step in the workflow (below)



Screenshot 3 (below) is what I want to identify the max step in the workflow which means we are at current step


Many thanks

Greg

[Greg888]

WOW! I have been beating my head against a wall for so long with this! 

I never thought MINX would be the thing.

That works an absolute treat!

[AntonioM · Accepted solution]

Hi, what you want is definitely doable. Something like

Column = 
VAR stepno = 'Table'[StepNo]
RETURN
IF(
    MINX(
        FILTER(
            'Table',
            'Table'[DocNumber] = EARLIER('Table'[DocNumber]) &&
            ISBLANK('Table'[TaskStartDate]) && ISBLANK('Table'[TaskEndDate])
        ),
        'Table'[StepNo]
    ) = stepno,
    1
)

Will find the current step for a given DocNumber and flag it with a 1.

Do the Revision and ID columns change as well or are the steps just for a certain DocNumber? If they do then you could add in either 'Table'[Revision] = EARLIER('Table'[Revision]) or 'Table'[ID] = EARLIER('Table'[ID]) into the filter.

Is that what you're after?

[Greg888]

Thanks for this that worked a treat but I have wandered headlong into a new problem .

I also want to identify the last step when TaskStartDate both have an end date but do not have a next step, whilst keeping the logic of the blanks as previously.

I do have a MaxStep column in the table which is the Max Step of the document group.
So in the example below the document has reached step 5 of 5

So all the logic before worked fine but I wanted to also say all of that but add an IF to work out that the flow had reached the last step (no more blank rows).



Its' getting to be a complex process and I may have to approached it differently.

Any more help greatley appreciated though