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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |