Forum Discussion
Using a filter from a different table to hilight a related column
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:
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
4 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
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/1447523Thank 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
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
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.
