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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.