How To: Filter Matrix where any of Three Columns match a Single Slicer

[AlexS2023]

Hello,

I have a Matrix based on a table (EmpSchedules) which shows where Employees will be on a given date (see example pic - the code refers to a Site). I would like to filter this by the respective Line Managers of each person. But there's a catch.

The Line Manager data is stored in a seperate related table (EmpDetails), but there are THREE columns for line managers. This is because some employees have a WeeklyManager, some have MonthlyManager, and some have an OperationsManager. Some have more than one, and a Manager can appear in any or all of the columns.

What I want is: a single slicer which the Managers can use to show only the Employees who they are managing. I have no idea how to do this.

After some searching, I created a Slicer which shows all the possible options, but I can't workout how to link that to the Visualisation above.

SlicerTable = DISTINCT(UNION(VALUES(EmpDetails[MonthlyManager]), VALUES(EmpDetails[WeeklyManager]), VALUES(EmpDetails[OperationsManager])))

Any ideas greatly appreciated 😄

Thanks

Alex

[Anonymous]

The easiest thing to do would be to create a separate dimension table with 2 columns, 1 for employees and 1 for the various managers; such that you'd have one row per employee per manager. Then if you create a relationship between your main fact table and this one, you can create a slicer with the manager column from this table. Only problem here being that it would filter the table so they can only see the employees responsible for them. To elavate this, if you have a table with a column for the managers emails and a column for the responsible employees. You could create a measure using the USERPRINCIPALNAME() function (this returns the email of the person viewing the report in power bi service) to change the colour of the columns where either the weekly, monthly, or operations manager email matches the value. So it would be automatic and would just highlight the one's they are responsible for. Hope this helps, let me know if you have any more questions.

[AlexS2023]

---

@Anonymous wrote:

...create a separate dimension table with 2 columns, 1 for employees and 1 for the various managers; such that you'd have one row per employee per manager.

---

Thanks Toby, any chance you could elaborate? How exactly would one achieve this? PBI newby here 🙂

Thanks,

[Anonymous]

The way to achieve the second solution I mentioned would first be to create a table with the users email addresses for your organisation in one column and their name as it appears in the other tables in another so it would look like the below:
User Email                       |  User

Tony.Stark@gmail.com   |   Tony

Alan.Doe@gmail.com     |   Alan

You can create this table however you prefer, in excel, sharepoint, directly in power bi doesn't really matter. Import that into your power BI report then you will need the following measure:

Test = 

/*This will filter the table we created earlier to only return the row with the user viewing the report, it won't work in destop as there is technically no user viewing the report. To test it you can either set this variable to be one of the emails in the table or you can publish it and view it (as long as you are one of the users in the table).*/

VAR _User = CALCULATE(SELECTEDVALUE('UserTable'[User]), FILTER('UserTable),

                                                                                                 [User Email] = USERPRINCIPALNAME())

/*This is what the measure returns, it is a colour based on whether the field is equal to the user that we have viewing the report, in this case I have put weekly manager, you can create a separate measure for both monthly and operations manager*/

RETURN

IF(SELECTEDVALUE('YourTable'[Weekly Manager]) = _User, "Insert your colour or colour hexcode here", BLANK())

To actually use this measure you will want to click on the matrix visual in your report, then find 'Cell elements' choose the series that you want to apply the formatting to (you can do this one by one for each value if you want them all to be highlighted) select the 'fx' button next to whichever colour you want to change, presumably background:

This will open a dialogue box, change 'Format Style' to Field Value then under 'What field should we base this on' choose the measure we created here:

If you instead wanted to apply colour where ANY  of the manager fields matched then use the following instead:

Test = 

VAR _User = CALCULATE(SELECTEDVALUE('UserTable'[User]), FILTER('UserTable),

                                                                                                 [User Email] = USERPRINCIPALNAME())

RETURN

IF(SELECTEDVALUE('YourTable'[Weekly Manager]) = _User

   || SELECTEDVALUE('YourTable'[Monthly Manager]) = _User

   || SELECTEDVALUE('YourTable'[Operations Manager]) = _User,

"Insert your colour or colour hexcode here", BLANK())

The two | symbols function as an OR operator, so this measure will return the chosen colour whenever one or more of the fields matches the user viewing the report, the implementation would be the same as before, you could also use a switch true to return different colours depending on which field matches:

VAR _User = CALCULATE(SELECTEDVALUE('UserTable'[User]), FILTER('UserTable),

                                                                                                 [User Email] = USERPRINCIPALNAME())

RETURN

SWITCH(TRUE(),

(SELECTEDVALUE('YourTable'[Weekly Manager]) = _User, "Insert your colour or colour hexcode here"

   SELECTEDVALUE('YourTable'[Monthly Manager]) = _User, "Insert your colour or colour hexcode here"

   SELECTEDVALUE('YourTable'[Operations Manager]) = _User, "Insert your colour or colour hexcode here"

, BLANK())

Hope this helps, let me know how you get on

[AlexS2023]

Thanks for such a detailed answer. I actually meant the first solution, creating a Dimension table for Employees and their managers. I assume the aim is to create something like this:

Name - - - - Manager

Michael - - - Tony

Michael - - - Jane

Olive - - - - - Jane

Jimothy - - - Jane

Jimothy - - - Tony

I could do it manually, but I want it to update when my data changes. How would I do that?

Thanks again 🙂

[Anonymous · Accepted solution]

I made a little sample dataset in power BI (hopefully similar to yours) to test with. If you go to the top ribbon and click on transform data:

If you open it up you will see the tables you have in your model, notably EMP Schedule and EMP details, if you click on the EMP Details table and then CTRL+C to copy and CTRL+V to paste, you should see that another table appears that is the same as EMP Details (I have renamed it to EMP Unpivot here):

This means that whenever data in the EMP Details table changes, so will this one, the only steps in here is the unpivot, so you'd you'd just right click on the Employee name field and you will see a dialogue box:

Select the 'Unpivot Other Columns' option that I've highlighted in the above and then your table should look like this (basically in the format I mentioned in my previous post):

The only other thing I did was rename the columns to be more meaningful, below you can see the relationships in the modelling view:

It's not best practice to use 2 way relationships when they are many to 1 but I think it's fine in this case, as doing anything differently would require more thinking and probably restructuring the data model and tables individually. The result is as below:

Base table with no filter selected:

Table with Alan selected:

You don't need all those columns there, just to show you that it is doing what it should be doing, if you want to remove the blank option from the slicer, click on it and in the Filters tab uncheck blank from Manager Name as below:

You can also remove these blanks within the power query (transform data) window but I left it in. Just in case you wanted to be able to see who didn't have a manager and then assign one to them. 

Hope this works for you, let me know if you have any trouble!

[AlexS2023]

Thank you!!! I noticed the Cross Filter Direction has to be 'both' for this to work, don't know why, but it works 🙂

You're the best. Thanks.

[AlexS2023]

Just to illustrate further, if this were my EmpDetails table...

And I choose "Tony" in the slicer,

I would expect to see only the highlighted records (because Tony is either their weekly- monthly- or operations-manager):

Any ideas how to make this happen? TIA 🙂