Filter a visual by values of another column

[mattlawrence]

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

[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

[v-danhe-msft]

Hi @mattlawrence,

Could you please share some sample data to have a test and post your desired result if possible?

Regards,

Daniel He

[mattlawrence]

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 · Accepted solution]

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

[Greg_Deckler]

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