Scatter Chart Legend Not Filtering Measure in Tooltip

[BJRoth]

This is a simplified version of the issue which was encountered whilst using a much larger set of data.

I have three tables of data, "Departments", "Products" and "Values". The structure and relationships of these tables can be seen below.

Relationships

I also have a measure called "Top Product" to identify the top product, which works in a table with max value, department and date columns, and as a tooltip in other visuals such as the Line Chart, where the department is the legend, max of value is the value and the date is the axis.

Top Product Measure

However, the scatter chart doesn't seem to take into account the legend as in other visuals, meaning the measure doesn't know which department to look for and so just takes the first Product where Value = MAX(Value) from all departments. This results in the tooltip giving the same "Top Product" for all Departments, as seen below.

Scatter Chart - WomensScatter Chart - MensScatter Chart - Kids

Is there a way around this, so that the department is considered in the measure? Or is this just a quirk/bug of Power BI/the scatter chart visual I'll have to work around?

[v-frfei-msft · Accepted solution]

Hi @BJRoth ,

Based on my test, I can reproduce your issue here. Thanks for pointing it out. I have reported it to PG. Once fixed, will let you know 🙂

[Anonymous]

Hi, any guess as to when this will be fixed? Still experiencing the issue. Thanks!

[v-frfei-msft]

Hi @BJRoth ,

I think you should use allexcept in your filter such as :

measure = calculate(expression ,filter(allexcept(your table, [legend]),filter condition))

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[BJRoth]

Hi @v-frfei-msft 

I think allexcept is what I was hoping for in terms of forcing the legend as a filter, however it just breaks my measure, which can't seem to return anything with allexcept included.

I can't provide the live data, nor access any cloud sharing tools like One Drive, due to company policies. However, below is some sample data I created for the mock scenario, which does replicate the issue.

In terms of desired output, if you create a table with the columns "Department", from the department table, "Top Product", the measure, "Max of Value" and "DateValidFor", then select any dot on a scatter chart using the same data, the table will show the information that should appear in the tooltip. 

Product

id	Product
0	Shirt
1	Trousers
2	Dress
3	Skirt
4	Blouse
5	Shoes
6	T-Shirt

Department

id	Department
1	Mens
2	Womens
3	Kids

Values

id	ProductID	DepartmentID	DateValidFor	Value
1	0	1	01/01/2018	50
2	0	2	01/01/2018	24
3	0	3	01/01/2018	38
4	1	1	01/01/2018	33
5	1	2	01/01/2018	35
6	1	3	01/01/2018	49
7	2	1	01/01/2018	33
8	2	2	01/01/2018	35
9	2	3	01/01/2018	16
10	3	1	01/01/2018	23
11	3	2	01/01/2018	30
12	3	3	01/01/2018	42
13	4	1	01/01/2018	34
14	4	2	01/01/2018	16
15	4	3	01/01/2018	22
16	5	1	01/01/2018	10
17	5	2	01/01/2018	51
18	5	3	01/01/2018	30
19	6	1	01/01/2018	43
20	6	2	01/01/2018	36
21	6	3	01/01/2018	59
22	0	1	01/02/2018	29
23	0	2	01/02/2018	32
24	0	3	01/02/2018	57
25	1	1	01/02/2018	38
26	1	2	01/02/2018	55
27	1	3	01/02/2018	28
28	2	1	01/02/2018	44
29	2	2	01/02/2018	43
30	2	3	01/02/2018	41
31	3	1	01/02/2018	49
32	3	2	01/02/2018	55
33	3	3	01/02/2018	25
34	4	1	01/02/2018	31
35	4	2	01/02/2018	49
36	4	3	01/02/2018	34
37	5	1	01/02/2018	34
38	5	2	01/02/2018	44
39	5	3	01/02/2018	28
40	6	1	01/02/2018	34
41	6	2	01/02/2018	30
42	6	3	01/02/2018	44
43	0	1	01/03/2018	50
44	0	2	01/03/2018	49
45	0	3	01/03/2018	53
46	1	1	01/03/2018	17
47	1	2	01/03/2018	18
48	1	3	01/03/2018	12
49	2	1	01/03/2018	56
50	2	2	01/03/2018	59
51	2	3	01/03/2018	58
52	3	1	01/03/2018	26
53	3	2	01/03/2018	35
54	3	3	01/03/2018	39
55	4	1	01/03/2018	41
56	4	2	01/03/2018	30
57	4	3	01/03/2018	42
58	5	1	01/03/2018	45
59	5	2	01/03/2018	43
60	5	3	01/03/2018	31
61	6	1	01/03/2018	59
62	6	2	01/03/2018	29
63	6	3	01/03/2018	28
64	0	1	01/04/2018	20
65	0	2	01/04/2018	49
66	0	3	01/04/2018	58
67	1	1	01/04/2018	54
68	1	2	01/04/2018	36
69	1	3	01/04/2018	54
70	2	1	01/04/2018	52
71	2	2	01/04/2018	53
72	2	3	01/04/2018	57
73	3	1	01/04/2018	12
74	3	2	01/04/2018	13
75	3	3	01/04/2018	41
76	4	1	01/04/2018	56
77	4	2	01/04/2018	14
78	4	3	01/04/2018	25
79	5	1	01/04/2018	18
80	5	2	01/04/2018	38
81	5	3	01/04/2018	17
82	6	1	01/04/2018	31
83	6	2	01/04/2018	44
84	6	3	01/04/2018	47
85	0	1	01/05/2018	52
86	0	2	01/05/2018	27
87	0	3	01/05/2018	60
88	1	1	01/05/2018	35
89	1	2	01/05/2018	54
90	1	3	01/05/2018	42
91	2	1	01/05/2018	44
92	2	2	01/05/2018	47
93	2	3	01/05/2018	49
94	3	1	01/05/2018	40
95	3	2	01/05/2018	41
96	3	3	01/05/2018	22
97	4	1	01/05/2018	56
98	4	2	01/05/2018	28
99	4	3	01/05/2018	36
100	5	1	01/05/2018	59
101	5	2	01/05/2018	24
102	5	3	01/05/2018	33
103	6	1	01/05/2018	13
104	6	2	01/05/2018	25
105	6	3	01/05/2018	40
106	0	1	01/05/2018	55
107	0	2	01/05/2018	50
108	0	3	01/05/2018	43
109	1	1	01/05/2018	51
110	1	2	01/05/2018	16
111	1	3	01/05/2018	10
112	2	1	01/05/2018	16
113	2	2	01/05/2018	43
114	2	3	01/05/2018	57
115	3	1	01/05/2018	28
116	3	2	01/05/2018	35
117	3	3	01/05/2018	21
118	4	1	01/05/2018	22
119	4	2	01/05/2018	36
120	4	3	01/05/2018	23
121	5	1	01/05/2018	48
122	5	2	01/05/2018	38
123	5	3	01/05/2018	28
124	6	1	01/05/2018	48
125	6	2	01/05/2018	45
126	6	3	01/05/2018	15
127	0	1	01/06/2018	46
128	0	2	01/06/2018	33
129	0	3	01/06/2018	51
130	1	1	01/06/2018	56
131	1	2	01/06/2018	34
132	1	3	01/06/2018	21
133	2	1	01/06/2018	54
134	2	2	01/06/2018	25
135	2	3	01/06/2018	23
136	3	1	01/06/2018	11
137	3	2	01/06/2018	45
138	3	3	01/06/2018	31
139	4	1	01/06/2018	11
140	4	2	01/06/2018	17
141	4	3	01/06/2018	21
142	5	1	01/06/2018	18
143	5	2	01/06/2018	44
144	5	3	01/06/2018	55
145	6	1	01/06/2018	43
146	6	2	01/06/2018	30
147	6	3	01/06/2018	54