Hello,
I'm trying to create a shape map visual based on a 3 digit zip. The 3 digit zip is a created column based on the full zip code, so each 3 digit zip can repeat many times throughout the data. I've mocked up a sample dataset below with the key fields. There are more fields in the actual dataset that act as filters on the page as well.
| ShippingPostal | ShippingPostal_Substring | Id |
| 12333 | 123 | 3wer |
| 12345 | 123 | 1111 |
| 12346 | 123 | 111a |
| 12347 | 123 | 2345 |
| 12361 | 123 | q234 |
| 12399 | 123 | d43a |
| 15533 | 155 | 342a |
| 15644 | 156 | 4rfd |
| 15678 | 156 | 678s |
| 19811 | 134 | a321 |
| 19834 | 198 | df34 |
| 19845 | 198 | f4er |
| 19856 | 124 | 5432 |
| 19870 | 198 | 1234 |
| 13423 | 134 | u890 |
I need to group by each ShippingPostal_Substring and take a count of Id (or rowcount based on ShippingPostal_Substring).
| ShippingPostal_Substring | CountOfId |
| 123 | 6 |
| 156 | 2 |
| 198 | 5 |
| 155 | 1 |
| 134 | 1 |
Once I have the count I need to create a label based on the count value (<2, 2-5, >=5).
| ShippingPostal_Substring | CountOfId | Label |
| 123 | 6 | >=5 |
| 156 | 2 | 2-5 |
| 198 | 5 | >=5 |
| 155 | 1 | <2 |
| 134 | 1 | <2 |
The problem that I'm running into is I need the label to be a column because it is acting as the legend in my map. I can get this all to work using measures, but that doesn't help me with the visual.
I have tried using the Group By functionality in the query editor, but this gives a static count value and I need the count to update based on the filters applied on the page. The label likewise must update as filters are applied. If the count value started at 6 with no filters applied and the filters dropped the count down to 3, I need the map to display the filtered count of 3 with a label of "2-5", not 6 with a label of ">=5".
I have tried to create a calculated column for the count and put it into a table visual, let's call it [Count123]. If the field is left as "Don't Summarize", [Count123] shows as 1 for every PostalShipping_Substring; if the field is set to "Sum", the count displays correctly. Example of what I see from the table visual is below:
| ShippingPostal_Substring | Count123 (Don't Summarize) | Count123 (Sum) |
| 198 | 1 | 5 |
| Total | | 5 |
I have not been able to create a column that correctly counts the Id's that can then have a label correctly applied to it. The fact that the label has to be a column to work in the map visual is what is tripping me up.
Any suggestions of how to create a dynamic count with a column label that updates based on filters and slicers on the page would be very much appreciated.
