Forum Discussion
Scatter Chart Legend Not Filtering Measure in Tooltip
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?
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 🙂
4 Replies
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 ,
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.
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
