Filter columns of matrix by slicer

[stocal]

Hello!

I'm trying to build a matrix visualization to indicate when a particular employee is trained to operate a particular machine, and need to filter which columns appear in the matrix depending on the user's filter selections. The data I'm working with is split between 3 tables, which store information about each employee, each machine, and each training combination:

Employee table example (named "Roster")

EmplID	Name	Line	Shift
1	Bob, Jim	A	1
2	Appleseed, Johnny	B	1
3	Greg, Craig	C	1
4	Smith, John	A	2
5	Doe, Jane	B	2
6	Mist, Sierra	C	2
7	Pepper, Doctor	A	3
8	Adams, Amy	B	3
9	Underwood, Rachel	C	3
10	Man, Bat	A	1
11	Woman, Wonder	B	2
12	Lantern, Green	C	3
13	Boddy, Mr.	A	1
14	Schmo, Joe	B	2
15	Adams, Adam	C	3

Machine table example (named "Operation")

Op_ID	Op_Desc	Process	Op_Shift_1	Op_Shift_2	Op_Shift_3
1	Build	A	1	1	1
2	Destroy	A	1	1	1
3	Climb	A	1	0	0
4	Descend	A	1	0	0
5	Swim	B	1	0	1
6	Run	B	1	1	0
7	Leave	B	1	0	0
8	Eat	C	0	1	1
9	Hunt	C	0	1	0
10	Fly	C	1	0	1

Training table example (named "Validation")

Op_ID	Emp_ID	Val_Pass
1	1	1
1	2	1
1	3	1
2	1	1
2	4	1
2	5	1
3	6	1
4	6	1
5	7	1
5	8	1
6	1	0
6	2	1
6	5	1
6	7	1
7	9	1
8	10	0
9	11	1
9	12	1
10	13	1
10	10	1

There is also a fourth table, Department, which has a single calculated column containing all the unique values of Line from Roster. The relationship diagram looks like this:

I've managed to create the visual below using a matrix visualization and putting Roster[Name] and Roster[EmplID] in the rows, Operation[Op_Desc] in the columns, and a measure I created for the values. The important part of the measure is [Passes], which is just SUM('Validation'[Val_Pass]), but I have it wrapped in another measure that calculates custom totals for me.

Code for the measure with custom totals:

MatrixTotalFormat = 
// If both dimensions have a single value, evaluate the measure normally
IF(HASONEVALUE('Roster'[EmplID]) && HASONEVALUE('Operation'[Op_Desc]), IF([Passes] == 1, "✓", BLANK()),
    IF(AND(HASONEVALUE('Operation'[Op_Desc]), NOT(HASONEVALUE('Roster'[EmplID]))),
        // If operation has a single value, but roster does not, evaluate total for this operation.
        VAR A =
        DIVIDE(
            SUM('Validation'[Val_Pass]),
            CALCULATE('Validation'[Employees], ALLSELECTED('Roster'[EmplID]))
        )
        RETURN
        IF(ISBLANK(A), 0, A),

        // If the above fails, check if roster has a single value and operation does not. 
        // If so, evaluate the total for this employee. 
        IF(AND(HASONEVALUE('Roster'[EmplID]), NOT(HASONEVALUE('Operation'[Op_Desc]))),
            VAR B =
            DIVIDE(
                SUM('Validation'[Val_Pass]),
                CALCULATE('Validation'[Operations], ALLSELECTED('Operation'[Op_Desc]))
            )
            RETURN
            IF(ISBLANK(B), BLANK(), B),
            
            // Neither dimension has a single value. Calculate the total matrix completion.
            VAR C =
            DIVIDE(
                SUM(Validation[Val_Pass]),
                CALCULATE(Validation[Operations] * Validation[Employees])
            )
            RETURN
            IF(ISBLANK(C), BLANK(), C)
        )
    )
)

Currently, I am slicing the matrix by Line, Shift, and Op_Desc. What I need to be able to do is when a shift is selected, look at the corresponding shift column in Operation (e.g. if Shift 1 is selected in the slicer, look at Op_Shift_1), and only show that machine in the matrix if its value in that column is 1(which means it is a core machine for this shift). If possible, it would be nice if multiple shifts are selected that a machine would appear if it has a 1 in any of the corresponding shift columns, but this isn't necessary.

The difficult part is that I also have several other parts that interact.

1. There is a second matrix that looks the same as the first except that it contains all the employees that aren't part of the selected line but are trained on a machine.
2. There are two other matrices that correspond to the first two except that they contain only the machines that have a 0 in the corresponding shift column for the selected shift (non-core machines).
3. I need a machine to show up in the matrix even if no employee is trained on it.
4. In the upper two matrices, I need employees to show even if they aren't trained on any machine. In the lower two matrices, I only want employees to show up if they have an entry in the Validation table for at least one of the machines selected.

As a result, there are four matrices: Employees on the selected line by core machines, employees on selected line by non-core machines, employees not on the selected line by core machines, and employees not on the selected line by non-core machines. 

This image is what it should look like, except that "Climb" and "Descend" should only appear in the right two matrices (because those are non-core for 2nd shift), and "Build" and "Destroy" should only appear in the left two matrices (because those are core for 2nd shift):

If no shift is selected, it's fine if all machines appear on both sides:

The whole .pbix file with this sample data can be downloaded here: https://drive.google.com/file/d/1MsSmgQoH3GAtQr3mNnGw3k_bIYubFOdZ

Sorry if this is too much detail, the number of moving pieces in this has become really complex and I'm having some difficulty keeping track of what everything needs to do myself. I figured it would be better to be as detailed as possible and let people skip over whatever is unnecessary than make a million follow-up posts haha. I'm also totally open to trying a different visualization if this can't be achieved using a normal matrix (Although a matrix solution would be preferable).

Thanks!

[v-stephen-msft]

Hi @stocal ,

Sorry, although you have explained it in detail, I am still not very clear. When you filter in the Shift field of the slicer, what is your expected result? It would be best if you can give the expected result in a matrix style result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stocal]

Hello @v-stephen-msft, thanks for the response! I'm sorry I wasn't able to explain the requirement clearly.

I need to change which columns appear in the matrices depending on the selected shift. Using just the upper-left matrix as an example, this is what it should look like (and currently does) with no shift selected:

If shift 1 is selected, only the machines in the Operation table that have a 1 in the Op_Shift_1 column should appear as columns in the matrix. So, "Eat" and "Hunt" should not appear in the matrix if shift 1 is selected because they have a 0 in the Op_Shift_1 column. Expected result:

If shift 2 is selected, the requirement is the same except the relevant column is Op_Shift_2, so only "Build", "Destroy", "Run", "Eat", and "Hunt" should appear in the matrix because all other machines have a 0 in the Op_Shift_2 column. Expected result:

The same pattern applies for shift 3:

If you know of a way to get that working, that would be a fantastic start! The other 3 matrices are just variations of this requirement, so if I had an example to solve this one I could probably figure out the others.

Thanks!