Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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?
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!
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?
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
99 | |
69 | |
46 | |
39 | |
33 |
User | Count |
---|---|
163 | |
110 | |
61 | |
51 | |
40 |