Max Count Help

[kdrees21]

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:

Location	Shift	Weekday	Customer ID	Max (Desired Output)
A	1	Monday	1	3
A	1	Monday	2	3
A	1	Monday	3	3
A	1	Tuesday	4	3
A	2	Monday	5	3
A	2	Tuesday	6	3
A	2	Tuesday	7	3

[AlexisOlson · Accepted solution]

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]

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]

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