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:
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.
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).
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.