Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.