Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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.
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?
Solved! Go to 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 🙂
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 🙂
Hi, any guess as to when this will be fixed? Still experiencing the issue. Thanks!
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.
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 |
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |