Mode of MAX values for each reaction plan

[bimmylee]

I have reaction plans that go into effect whenever there is a problem on the floor. Each plan has a sequence of ordered steps that a user will progress through until the problem is solved; the steps that are used are marked "complete" while the other unused steps remain with a status of "defined", as shown below:

A single reaction plan (indicated by the "tasklistid" column) may be used multiple times and have a different step used each time to resolve the problem. What I am having difficulty calculating is the most common step used as resolution for each reaction plan. Essentially, it is the MODE of the "taskorder" column, but only where it is the max value for that particular reaction plan AND where taskstatus is "complete". I have looked at this resource for calculating Mode in DAX, but I don't know how to incorporate the other two critera, particularly the "max" bit. This is what I have so far:

Most Common Step As Resolution = MINX (
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( fact_subtask_snapshot[taskorder] ),
            "Frequency", CALCULATE ( COUNT ( fact_subtask_snapshot[taskorder] ), fact_subtask_snapshot[taskstatus] = "complete" )
        ),
        [Frequency],
        0
    ),
    fact_subtask_snapshot[taskorder]
)

This of course always gives an answer of "1" since every plan has at least a Step 1:

I am at a loss as to how to tell it to instead look at the max completed step # for each plan when calculating the mode; this might involve some sort of "grouping" function as well. Lastly, if the above can be solved, I also need to count the number of times the most common resolution step was used, which would hopefully be a simple matter at that point.

Thanks in advance!

-Chris

[bimmylee]

This solution is brilliant and works perfectly. Thank you so much!

[AlexisOlson · Accepted solution]

The first step is to figure out the maximal complete taskorder for each taskid. Then you can proceed to calculate the frequency and extract the most frequent.

Most Common Step As Resolution =
VAR MaxOrder =
    ADDCOLUMNS (
        VALUES ( fact[taskid] ),
        "@MaxStep", CALCULATE ( MAX ( fact[taskkorder] ), fact[taskstatus] = "complete" )
    )
VAR AddFrequency =
    ADDCOLUMNS (
        VALUES ( fact[taskorder] ),
        "@Frequency",
            VAR CurrStep = fact[taskorder]
            RETURN
                COUNTROWS ( FILTER ( MaxOrder, [@MaxStep] = CurrStep ) )
    )
RETURN
    MINX ( TOPN ( 1, AddFrequency, [@Frequency], 0 ), fact[taskorder] )

If you want the frequency instead of the corresponding step, simply replace the final argument of MINX with [@Frequency] instead of fact[taskorder].