cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Mode of MAX values for each reaction plan

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,
),
[Frequency],
0
),
)``````

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.

-Chris

1 ACCEPTED SOLUTION
Super User

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 =
)
"@Frequency",
RETURN
COUNTROWS ( FILTER ( MaxOrder, [@MaxStep] = CurrStep ) )
)
RETURN

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

2 REPLIES 2
Frequent Visitor

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

Super User

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 =
)
"@Frequency",
RETURN
COUNTROWS ( FILTER ( MaxOrder, [@MaxStep] = CurrStep ) )
)
RETURN

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.