I'm trying to group a column of data (room capacities) through a measure into dynamic banded groups, eg 0-9, 10-19, 20-19 etc using a DAX measures and a capacity reference table, but I just can't seem to get the measure that will find the right results.
I'm not seeing the right results and would like to check why this is:
First, I'm attempting to represent the column of data as a measure, eg.
2m = SumX('Available Space', 'Available Space'[Final Capacity Estimate 2m]*1)
Is this correct?
Secondly I have created a reference table of capacity groups as follows:
| Capacity | Min | Max |
| 0-9 | 0 | 9 |
| 10-19 | 10 | 19 |
| 20-29 | 20 | 29 |
Lastly, I've tried to find a way to created a DAX formula which will allow the first measure to be searched for the number of instances where a piece of data falls between the mins and maxes in the table as so:
Capacity Band 2m = CALCULATE ([2m],
FILTER(VALUES('Available Space'[Final Capacity Estimate 2m]) ,
COUNTROWS(FILTER('Capacity Bands', 'Available Space'[Final Capacity Estimate 2m] >= 'Capacity Bands'[Min] && 'Available Space'[Final Capacity Estimate 2m] < 'Capacity Bands'[Max]))))
This doesn't appear to be returning the correct data, and I'm not sure which calculation this is due to. Has anyone ever grouped through a measure like this before?
Some advice or an alternative answer would be extremely helpful.
