Using a filter from a different table to hilight a related column

[suziecameron]

I have created a simplified version of a visual where I can select an employee from a staff list in the first table, and I can see the other employees based in (1) the same team, and (2) the same location as the selected employee:

Full Table:

Filtered Table:

I would like to be able to hilight the selected employee's row in the Team and Location tables respectively, something like this: 

However, because of the way I have set up the relationships, I cannot find a way to do it.

This is the model view:

The "Teams" and "Locations" are Dimension Tables, and the logic for the "Team Table" and the "Loc Table" are as follows:

Team Table =

SUMMARIZE('Employees','Employees'[Team],'Employees'[ID])

Loc Table =

SUMMARIZE('Employees','Employees'[Location],'Employees'[ID])

Does anyone have any suggestions on how to improve this set-up? Many thanks

[Anonymous · Accepted solution]

Hi  @suziecameron ,

Here are the steps you can follow：

1. Create measure.

Location_color =
var _select=
SELECTEDVALUE('Employee Table'[Location])
var _count=
COUNTX(ALLSELECTED('Employee Table'),'Employee Table'[Location])
return
IF(
HASONEFILTER('Employee Table'[Location])=FALSE(),BLANK(),
IF(
    MAX('Location DimTable'[Location])=_select,"red",
    BLANK())
)

team_color =
var _select=
SELECTEDVALUE('Employee Table'[Team])
return
IF(
HASONEFILTER('Employee Table'[Team])=FALSE(),BLANK(),
IF(
    MAX('Team DimTable'[Team])=_select,"yellow",
    BLANK())
)

2. Place [Location_color] into Location DimTable Visual and select [Location] - Conditional Formatting - Background Colour.

3. Place [team_color] into Team DimTableVisual and select [team] - Conditional Formatting - Background Colour.

4. Result:

Best Regards,

Liu Yang

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

[suziecameron]

Hello @Anonymous 

Thank you so much for your assistance here, I am very grateful!

The problem I am encountering is that in the DimTableVisual, I would like to see the full list of employees in that [loaction] or [team], with only the selected ID highlighted.

So assuming ID '7' was selected, as in your screenshot, in the DimLocation visual I would want to see a list of all IDs related to Location '2' (ie. IDs '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '21'), with ID '7' highlited. In the DimTeam visual I would was to see a list of all IDs related to Team 'A' (ie. IDs '1', '2', '3', '4', '5', '6', '7', '8'), with ID '7' highlited.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[suziecameron]

Thank you @lbendlin

Employee Table:

IDTeamLocation

1	A	1
2	A	1
3	A	1
4	A	2
5	A	2
6	A	2
7	A	2
8	A	2
9	A	2
10	B	2
11	B	2
12	B	2
13	B	2
14	B	2
15	B	4
16	B	3
17	B	3
18	C	3
19	C	3
20	C	3
21	C	2
22	C	1
23	C	1
24	C	1

Location DimTable:

Location

1

2

4

3

Team DimTable:

Team

A

B

C

The desired result is that I can select/filter for a specific employee from my 'Employees' table, and I can see a full list of other employees in that employee's team and location respectively, but with the selected/filtered employee highlighted or marked in some way for readability, for example:

I would happily share the full .pbix file, but I don't know how to do it since I can't attach it.

Thanks again for the help