Clustering / Visualizing Ordered Process Steps

[Anonymous]

Hi all,

I am looking for some inspiration or an exact solution to achieve clusters of process steps. My data looks like the following:

Key	Process_Name	Order
1	A	10
1	A	40
1	B	30
1	C	20
1	D	50
2	A	10
2	B	40
2	C	30
2	E	20
3	B	10
3	C	20
3	E	30
4	A	10
4	C	20
4	E	30

The significance of order is for each key, the process step will be complete according to the Order field sorted in ascending order. For example, the actual sequence for Key 1 is A - C - B - A.

I am looking to achieve the following through my data: a matrix / potential (cluster) where the values in the matrix represent a count over all keys where a process step procedes another process step. For example, Key 1's process is A-C-B-A giving the matrix of the following:

	A	B	C
A			1
B	1
C		1

The end result for the sample data provided will be as such:

	A	B	C	D	E
A			2	1	1
B	1		1
C		2			2
D
E			1

My question is twofold:

1. How can I achieve this using DAX and potentially PowerQuery? 
2. Is this the best approach (are there custom visualizations available?) to visualize the commonality between process steps?

Thank you, any bit of guidance or advice helps!

[daxer-almighty · Accepted solution]

Key Count = 
// Count the Keys where the current preceeding process
// is the direct ancestor of the current succeeding
// process (based on the Order column) in the current
// context. This means the measure is sensitive to
// any filters put on the T table.
var OnlyOneCombinationVisible = COUNTROWS( 'Process Pairs' ) = 1
var Result =
    if( OnlyOneCombinationVisible,
        var CurrentPrecedingProcess = 
            SELECTEDVALUE( 'Process Pairs'[Preceding Process] )
        var CurrentSucceedingProcess =
            SELECTEDVALUE( 'Process Pairs'[Succeeding Process] )
        return
            SUMX(
                CALCULATETABLE(
                    SUMMARIZE(
                        T,
                        T[Key],
                        T[Order]
                    ),
                    KEEPFILTERS( 
                        T[Process] = CurrentPrecedingProcess
                    )
                ),
                var CurrentKey = T[Key]
                var CurrentOrder = T[Order]
                var NextProcess =
                    MAXX(
                        TOPN(1,
                            FILTER(
                                T,
                                and(
                                    T[Key] = CurrentKey,
                                    T[Order] > CurrentOrder
                                )
                            ),
                            T[Order],
                            ASC
                        ),
                        T[Process]
                    )
                return
                    if( NextProcess = CurrentSucceedingProcess, 1 )
        )
    )
return
    Result