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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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