DAX - Return multiple column rows from a single column row based on criteria

[monsoonnut]

I am creating a stores report in Excel 2016 using PowerPivot
I have been asked to include measures relating to any stores that are within 10% of the size (m³) of a given store, i.e. return a list of stores that are within that size threshold.
I do not need to query against more than one store at a time.

I have a stores dim table with lots of data but the relevant columns are:

LOC_NO	SIZE
24	187
36	207
75	215
29	219
15	231
17	257
64	290
12	331
88	364
3	367
27	430

<tbody>
</tbody>

I have created measures for StoreSize, MaxSize and MinSize as comparators:
StoreSize= MAX(Stores[SIZE])
MaxSize= [StoreSize]+([ StoreSize]*0.1)
MinSize= [StoreSize]-([ StoreSize]*0.1)
By passing a single store to a pivot I need to return all other stores that are within 10% of its size.
I believe I need a duplicate copy of the stores table but after that I am stuck.
I have tried every iteration of calculate with differing filters I can think of but as I am returning multiple values there are always errors.
I am sure this is possible but cannot work out how!
Any ideas, comments, help would be appreciated.

 

[Matt Allington]

My guess is a second table that is not connected to the first table. You need to write a measure that will return a value if the store in the second table is within the threshold of the store selected in the first table. So the trick is to harvest the Max and min sizes in the initial filter context, then use that to create a measure to return a non blank value from the list in the second table. I would try this (not tested)

stores in threshold =calculate(table2[size],filter(table2,table2[size] <= [MaxSize] && table2[size] >= [MinSize] ))

put a slicer on table1[loc no] and select 1 store
put table2[loc no] on rows in a pivot and the measure above on values.

you can watch my presentation about evaluation contexts from the Microsoft Data Insights Summit here if you want to improve your knowledge and skills. My MDIS Presentations - Excelerator BI

 

[monsoonnut]

Thanks Matt, worked like a charm.

It is what I was trying to do but in my formula I was filtering the first table rather than the second!