Answered by:
Banding with just minimum value
Question

I use banding techniques very frequently to put my customers into categories based on sales, visits, etc. I'm looking for a pattern that bands patrons based on the minimum value of a metric. For example, I can group patrons with 15 visits, 610 visits, etc. What I want to do is band the patrons into 1 OR MORE visits, 5 OR MORE visits, 10 OR MORE visits, etc. So each patron would fall into a category based on the minimum number of visits. How can I modify the standard banding pattern to get this result?
Thank you.
Answers

Oh I did not realise that all of your bands were using a >= or equal to condition I assumed that it was just the high band that you were after. You could simplify the filter condition by removing the comparisons to the MAX column all together and it would still work (and would me marginally faster as we have eliminated a couple of operations)
TripsAgg := CALCULATE ( [Patrons18], FILTER ( VALUES ( J18HOT030[RESVS18] ), COUNTROWS ( FILTER ( res18rng, J18HOT030[RESVS18] >= res18rng[MIN] ) ) ) )
But you would have to use a disconnected table and a measure due to the fact that patrons can be in multiple bands which would not work for a column calc. Unfortunately with PowerPivot you have to put up with it trying to be helpful and warning you that there is a possible missing relationship.
http://darren.gosbell.com  please mark correct answers
 Marked as answer by domtrump Tuesday, July 2, 2019 9:46 PM
All replies

A simple solution is to use a really high value for your max in the banding. Or you could use a "special" value in your high band value and check for that. In the example below if you enter a [Max Price] banding of 1 it effectively ignores the high band and just does a >= [Min Price]
SalesRange :=
CALCULATE (
[SalesAmount],
FILTER (
VALUES ( Sales[Price] ),
COUNTROWS (
FILTER (
Ranges,
Sales[Price] >= Ranges[Min Price]
&& (Sales[Price] < Ranges[Max Price]  Ranges[Max Price] = 1)
)
) > 0
)
)http://darren.gosbell.com  please mark correct answers

OK, I was trying to make a calculated column like I normally do for banding which obviously wont work because patrons can appear in multiple ranges. So I followed your pattern and made a measure. It works well. PP yelled at me about a "relationship might be needed" but my banding table is a disconnected table so I just ignored it and it still worked (I assume that is normal or should it be connected in some way?).
Thank you.
My measure:
TripsAgg := CALCULATE ( [Patrons18], FILTER ( VALUES ( J18HOT030[RESVS18] ), COUNTROWS ( FILTER ( res18rng, J18HOT030[RESVS18] >= res18rng[MIN] && ( J18HOT030[RESVS18] < res18rng[MAX]  res18rng[MAX] = 1 ) ) ) ) )
Banding Table

Oh I did not realise that all of your bands were using a >= or equal to condition I assumed that it was just the high band that you were after. You could simplify the filter condition by removing the comparisons to the MAX column all together and it would still work (and would me marginally faster as we have eliminated a couple of operations)
TripsAgg := CALCULATE ( [Patrons18], FILTER ( VALUES ( J18HOT030[RESVS18] ), COUNTROWS ( FILTER ( res18rng, J18HOT030[RESVS18] >= res18rng[MIN] ) ) ) )
But you would have to use a disconnected table and a measure due to the fact that patrons can be in multiple bands which would not work for a column calc. Unfortunately with PowerPivot you have to put up with it trying to be helpful and warning you that there is a possible missing relationship.
http://darren.gosbell.com  please mark correct answers
 Marked as answer by domtrump Tuesday, July 2, 2019 9:46 PM
