Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
stocal
Frequent Visitor

Filter columns of matrix by slicer

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")

EmplIDNameLineShift
1Bob, JimA1
2Appleseed, JohnnyB1
3Greg, CraigC1
4Smith, JohnA2
5Doe, JaneB2
6Mist, SierraC2
7Pepper, DoctorA3
8Adams, AmyB3
9Underwood, RachelC3
10Man, BatA1
11Woman, WonderB2
12Lantern, GreenC3
13Boddy, Mr.A1
14Schmo, JoeB2
15Adams, AdamC3

 

Machine table example (named "Operation")

Op_IDOp_DescProcessOp_Shift_1Op_Shift_2Op_Shift_3
1BuildA111
2DestroyA111
3ClimbA100
4DescendA100
5SwimB101
6RunB110
7LeaveB100
8EatC011
9HuntC010
10FlyC101

 

Training table example (named "Validation")

Op_IDEmp_IDVal_Pass
111
121
131
211
241
251
361
461
571
581
610
621
651
671
791
8100
9111
9121
10131
10101

 

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:

pic.png

 

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.

stocal_1-1635869100323.png

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):

stocal_3-1635880839660.png

 

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

stocal_4-1635881066138.png

 

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!

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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.

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:

stocal_0-1636553201070.png

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:

stocal_3-1636554594518.png

 

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:

stocal_2-1636554555204.png

The same pattern applies for shift 3:

stocal_4-1636554647910.png

 

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!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.