none
Banding with just minimum value RRS feed

  • 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 1-5 visits, 6-10 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.

    Monday, July 1, 2019 5:52 PM

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
    Tuesday, July 2, 2019 8:47 PM
    Moderator

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

    Monday, July 1, 2019 10:42 PM
    Moderator
  • 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

    

    Tuesday, July 2, 2019 12:55 PM
  • 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
    Tuesday, July 2, 2019 8:47 PM
    Moderator
  • Got it. Thank you so much!

    -Dom

    Tuesday, July 2, 2019 9:46 PM