Count number of data points for dimension based on a subset of data
My data model looks like this:
We have a bunch of stores with competitors of various brands. For each competitor, we know the physical distance to the store and for each competitor's price, we know the delta to the price of the store.
I'm attempting to create a heatmap where the dimensions are the delta values and the brands (sorted by the distance to the nearest competitors). The measure is the count of competitor prices that match the delta dimension. But, we only want to consider prices for the nearest competitor of each brand.
For each store, we need to determine the nearest competitor of each brand. Let's call that set CLOSEST_COMPETITORS.
Only the prices of competitors in the set CLOSEST_COMPETITORS should be considered in the heatmap.
The brand dimension of the heatmap is sorted by the distance of the competitors in CLOSEST_COMPETITORS. If multiple stores are selected, then it should be the AVG distance of the nearest competitors per brand.
The range of values in the delta dimension is adjustable by the user. The default range will be between -10 and 10.
I have attached a test app that demonstrates the data model and a couple of screenshots that displays the expected output.
Example when store1 is selected:
Competitors A, D, and E are the closest one of each brand, to the store, so we will only consider the prices of these competitors. The heatmap will show the count of each delta.
I'm struggling to create a measure that filters out the prices that I don't care about. I think I can find the competitors for a single store with: