Forum Discussion

kdrees21's avatar
kdrees21
Helper I
4 years ago
Solved

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 ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Please check the below picture and the attached pbix file.

 

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] )
kdrees21's avatar
kdrees21
Helper I
4 years ago

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