cancel
Showing results for
Did you mean:
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:

 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
1 ACCEPTED SOLUTION
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.

3 REPLIES 3
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.

Super User

Hi,

Please check the below picture and the attached pbix file.

MAX desired output: =

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 (
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

Helper I

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors