cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kdrees21
Helper I
Helper I

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
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

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
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Picture1.png

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] )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors