How to determine the order of occurrence based on filter criteria and order of another column

[Anonymous]

I have a dataset about workflows with unique information about each step in a workflow. I have a column titled "critical" and I have the "order" of the steps per each workflow. I'm interested in finding the order of occurrence for when "critical"="true".

Note: The "order" of steps is not in chronological order so I need to account for that in my query/DAX function

Here is an example of my dataset:

Workflow ID	Order	Critical
23	0	false
23	3	true
23	1	true
23	2	false
23	4	false
54	0	true
54	1	false
54	3	true
54	2	false

I would like to return a column (possibly like the one below) so that I can use it in calculations to determine dates between these critical steps

Workflow ID	Order	Critical	Occurrence of Critical
23	0	false	null
23	3	true	2
23	1	true	1
23	2	false	null
23	4	false	null
54	0	true	1
54	1	false	null
54	3	true	2
54	2	false	null

Thank you for all help! I've been working on this problem for a while and haven't come up with a good way to handle the order of this "critical" column.

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

You can create a calculated column as below:

Column = IF('Table'[Critical]=FALSE(),BLANK(),RANKX(FILTER('Table','Table'[Workflow ID]=EARLIER('Table'[Workflow ID])&&'Table'[Critical]=TRUE()),'Table'[Order],,ASC,Dense))

Or a measure as below:

Measure = IF(SELECTEDVALUE('Table'[Critical])=FALSE(),"null",RANKX(FILTER(ALL('Table'),'Table'[Workflow ID]=MAX('Table'[Workflow ID])&&'Table'[Critical]=TRUE()),CALCULATE(MAX('Table'[Order])),,ASC,Dense))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!