cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors