Forum Discussion

kdrees21's avatar
kdrees21
Helper I
4 years ago
Solved

Max Count Help

I have data that shows Location, shift, and weekday, and customer ID. 

 

What I am trying to accomplish is finding the maximum number of customers on any day and any shift to determine the maximum customer limit at a location.

 

So I can say on any shift and on any day this location can hold x amount of customers. In the example below the max would be 3 at location A. If I can do this in a Measure that would be preferred. Any help is greatly appreciated.

 

My data looks like below:

 

LocationShiftWeekdayCustomer IDMax (Desired Output)
A

1

Monday13
A1Monday23
A1Monday33
A1Tuesday43
A2Monday53
A2Tuesday63
A2Tuesday73
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    This version should be simpler and more efficient:

    MaxCustomers = 
MAXX (
    SUMMARIZE (
        CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) ),
        Data[Location],
        Data[Shift],
        Data[Weekday],
        "@Count", COUNT ( Data[Customer ID] )
    ),
    [@Count]
)

     This should work as a measure or a calculated column.

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    This version should be simpler and more efficient:

    MaxCustomers = 
MAXX (
    SUMMARIZE (
        CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) ),
        Data[Location],
        Data[Shift],
        Data[Weekday],
        "@Count", COUNT ( Data[Customer ID] )
    ),
    [@Count]
)

     This should work as a measure or a calculated column.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

    MAX desired output: =

    VAR add_customerscount =
    ADDCOLUMNS (
    ALL ( Data ),
    "@customerscount",
    CALCULATE (
         VAR currentlocation =
         MAX ( Data[Location] )
         VAR currentshift =
         MAX ( Data[Shift] )
         VAR currentweekday =
         MAX ( Data[Weekday] )
         VAR newtable =
         FILTER (
         ALL ( Data ),
         Data[Location] = currentlocation
         && Data[Shift] = currentshift
         && Data[Weekday] = currentweekday
         )
         RETURN
         COUNTROWS ( newtable )
    )
    )
    VAR findmax_customerscount =
    GROUPBY (
    add_customerscount,
    Data[Location],
    "@maxcustomerscount", MAXX ( CURRENTGROUP (), [@customerscount] )
    )
    RETURN
    MAXX ( findmax_customerscount, [@maxcustomerscount] )
    • kdrees21's avatar
      kdrees21
      Helper I
      4 years ago

      This looks like it works, but my table has ~500k rows. Doesn't look like its optimal for this. Any other thoughts or suggestions?