Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to Solution.
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.
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.
Hi,
Please check the below picture and the attached pbix file.
MAX desired output: =
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.
This looks like it works, but my table has ~500k rows. Doesn't look like its optimal for this. Any other thoughts or suggestions?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |