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")
Machine table example (named "Operation")
Training table example (named "Validation")
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:
// If both dimensions have a single value, evaluate the measure normally
IF(HASONEVALUE('Roster'[EmplID]) && HASONEVALUE('Operation'[Op_Desc]), IF([Passes] == 1, "✓", BLANK()),
// If operation has a single value, but roster does not, evaluate total for this operation.
VAR A =
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.
VAR B =
IF(ISBLANK(B), BLANK(), B),
// Neither dimension has a single value. Calculate the total matrix completion.
VAR C =
CALCULATE(Validation[Operations] * Validation[Employees])
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.
- 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.
- 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).
- I need a machine to show up in the matrix even if no employee is trained on it.
- 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).