Forum Discussion
Filter a visual by values of another column
In a sports dataset (2 teams) I have a variety of different columns which show type of actions and performance measures. I need to be able to view the opposition's actions in a visual when filtering by one team. In my dataset there is Team and Opposition Team. Is it possible to use the values of Opposition Team (multiple teams in some cases) and apply a filter using DAX of these values? The fixtures are filtered by a slicer.
For example:
I wish to visualise the distance the striker of LA Galaxy runs in 5 (user selected) games. But in another visual need to visualise the distance the striker runs of all opposing teams in these 5 matches.
Many thanks,
Matt
Hi mattlawrence,
Based on my test, you could refer to below test:
Create a new table to list the distinct team_id:
New table = DISTINCT('sample data for community'[team_id])Create a measure:
Opposite meters = CALCULATE(SUM('sample data for community'[Metres]),FILTER('sample data for community','sample data for community'[team_id]<>SELECTEDVALUE('New table'[team_id])))Now you could use the 'New table'[team_id] as slicer to show the opposite meters:
You could also download the pbix file to have a view.
Regards,
Daniel He
5 Replies
- Greg_Deckler
Community Champion
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
v-danhe-msftMicrosoft Employee
Hi mattlawrence,
Could you please share some sample data to have a test and post your desired result if possible?
Regards,
Daniel He
Hi Daniel,
Thanks for your help, it is highly appreciated.
Here is some very simplified sample data:
ID FXID team_id MatchTime action Metres PosID opp_id 1 LATR LA 27 Run 3 Striker TR 2 LATR LA 32 Run 7 Midfield TR 3 LATR LA 35 Run 3 Defense TR 4 LATR LA 69 Run 6 Striker TR 5 LATR TR 8 Run 7 Midfield LA 6 LATR LA 50 Run 4 Defense TR 7 LATR LA 69 Run 5 Striker TR 8 LATR TR 42 Run 14 Midfield LA 9 LATR TR 63 Run 8 Defense LA 10 LATR TR 15 Run 11 Striker LA 11 LATR TR 21 Run 4 Midfield LA 12 LATR LA 43 Run 1 Defense TR 13 LATR LA 24 Run 6 Striker TR 14 LATR TR 57 Run 16 Midfield LA 15 LATR TR 55 Run 20 Defense LA 16 LATR TR 8 Run 3 Striker LA 17 LATR TR 51 Run 16 Midfield LA 18 LATR LA 18 Run 13 Defense TR 19 LATR LA 47 Run 16 Striker TR 20 LATR TR 11 Run 6 Midfield LA 21 LATR TR 39 Run 18 Defense LA 22 LATR TR 33 Run 15 Striker LA 23 LATR TR 42 Run 13 Midfield LA 24 LATR TR 39 Run 4 Defense LA 25 LAVN LA 90 Run 10 Striker VN 26 LAVN LA 26 Run 4 Midfield VN 27 LAVN LA 11 Run 15 Defense VN 28 LAVN LA 7 Run 19 Striker VN 29 LAVN VN 43 Run 10 Midfield LA 30 LAVN LA 26 Run 2 Defense VN 31 LAVN LA 90 Run 8 Striker VN 32 LAVN VN 22 Run 12 Midfield LA 33 LAVN VN 32 Run 11 Defense LA 34 LAVN VN 17 Run 11 Striker LA 35 LAVN VN 64 Run 4 Midfield LA 36 LAVN LA 84 Run 9 Defense VN 37 LAVN LA 0 Run 1 Striker VN 38 LAVN VN 5 Run 7 Midfield LA 39 LAVN VN 64 Run 3 Defense LA 40 LAVN VN 15 Run 13 Striker LA 41 LAVN VN 78 Run 7 Midfield LA 42 LAVN LA 20 Run 6 Defense VN 43 LAVN LA 60 Run 19 Striker VN 44 LAVN VN 28 Run 3 Midfield LA 45 LAVN VN 57 Run 13 Defense LA 46 LAVN VN 9 Run 11 Striker LA 47 LAVN VN 2 Run 5 Midfield LA 48 LAVN VN 72 Run 8 Defense LA 49 TRVN TR 32 Run 6 Striker VN 50 TRVN TR 36 Run 7 Midfield VN 51 TRVN TR 28 Run 8 Defense VN 52 TRVN TR 43 Run 17 Striker VN 53 TRVN VN 49 Run 13 Midfield TR 54 TRVN TR 3 Run 12 Defense VN 55 TRVN TR 65 Run 7 Striker VN 56 TRVN VN 5 Run 4 Midfield TR 57 TRVN VN 57 Run 15 Defense TR 58 TRVN VN 45 Run 1 Striker TR 59 TRVN VN 62 Run 6 Midfield TR 60 TRVN TR 58 Run 17 Defense VN 61 TRVN TR 34 Run 8 Striker VN 62 TRVN VN 64 Run 6 Midfield TR 63 TRVN VN 24 Run 12 Defense TR 64 TRVN VN 63 Run 4 Striker TR 65 TRVN VN 40 Run 20 Midfield TR 66 TRVN TR 19 Run 8 Defense VN 67 TRVN TR 49 Run 1 Striker VN 68 TRVN VN 85 Run 13 Midfield TR 69 TRVN VN 78 Run 4 Defense TR 70 TRVN VN 82 Run 1 Striker TR 71 TRVN VN 19 Run 16 Midfield TR 72 TRVN VN 16 Run 3 Defense TR FXID REF LATR Jones LAVN Jones TRVN Matthews There is a relationship between FXID.
MatchData FixtureData
FXID *:1 FXID
What I require in the blank box is the data for the opposition.
For instance when LA are playing I need to be able to show their meters and the opposition meters. This could be when the user selects a single team from the opposition or the user wishes to view how the oppositon perform generally against LA.
When filtering by team_id (which the user will be doing) all the data for the opposition is filtered out. I have tried using DAX functions like ALL, ALLSELECTED and SELECTEDVALUE (possibly wrongly) but this is proving difficult. I was wondering if you could help with the DAX or help with another solution.
In the real dataset there are many more slicers used and there are more relationships between the match data and other tables.
PBIX file here: https://drive.google.com/file/d/1xAHB-5sszaiNX6LY1NCFnFIYanuScP0I/view?usp=sharing
Many thanks,
Matt
- v-danhe-msft
Hi mattlawrence,
Based on my test, you could refer to below test:
Create a new table to list the distinct team_id:
New table = DISTINCT('sample data for community'[team_id])Create a measure:
Opposite meters = CALCULATE(SUM('sample data for community'[Metres]),FILTER('sample data for community','sample data for community'[team_id]<>SELECTEDVALUE('New table'[team_id])))Now you could use the 'New table'[team_id] as slicer to show the opposite meters:
You could also download the pbix file to have a view.
Regards,
Daniel He
- v-danhe-msft
Hi mattlawrence,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
